Being a database administrator you have asked to take support of other databases as well like Oracle, noSQL, Cloud PaaS and IaaS etc. In Indian service based company, we need attest more than one database knowledge and support experience to lead a database team as well. I have been working in IT more than 12 years and had supported Oracle not in top level and it was older days and I thought of writing lot once, I have strong working experience, but I did not get much after that. I just started again quarantine time, let us install 18c and do some test ourselves. Take a reference of old post:…
-
-
Backup share filer space alert notification Automon DBA SPs – AM20
This script will check the backup share filer free space and notify an alert. Table: use dbadata go drop table Backup_filers_name CREATE TABLE [dbo].[Backup_filers_name]( [filer_no] [int] IDENTITY(1,1) NOT NULL, [filer_name] [varchar](100) NOT NULL, [filer_name_original] [varchar](100) NULL, [filer_Satus] [varchar](20) NULL, [access_from_local] [varchar](100) NULL, PRIMARY KEY CLUSTERED ( [filer_name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) drop table Backup_filer_space_check create table Backup_filer_space_check (Filer_name varchar(500),output varchar(1000),date datetime) use DBAdata_Archive go drop table Backup_filer_space_check_final create table Backup_filer_space_check_final (Filer_name varchar(500),Free_space_GB varchar (1000),date datetime) */ /* update Backup_filers_name set filer_satus = 'Not in use' where filer_name in( '\\share\spbackup\', ) SP: /*…
-
AlwaysON SQL server database blocking SSISDB Active Operations
AlwaysON SQL server database blocking I had two blocking case on one of my alwaysON databases. Backup is third party tool and it got blocked by each other sessions. SSIS package deployed in SSIS folder and it run long and had blocking as well. The first one, tried to kill the session ID, it went in to rollback state and it took more than half day, no response. Fix: Just suspend the database in the primary AG this will remove the killed SPID. The second one, we can look into the Active Operations of packages. Integration service catalog – right click SSISDB – Active Operations
-
Larger transaction log file sync with recovery model alert notification Automon DBA SPs – AM19
This script will check the recovery model and larger log files and notify an alert, when it does not sync. Table: USE [DBAdata] GO drop table tbl_Large_logfile_sync_with_recovery_model_check CREATE TABLE [dbo].[tbl_Large_logfile_sync_with_recovery_model_check]( [servername] [sysname] NULL, [dbname] [sysname] NOT NULL, [filename] [sysname] NOT NULL, [recovery_model] [sysname] NOT NULL, [log_size] [int] NULL, [freespace] [varchar](50) NULL, [Drive_letter] [sysname] NULL, [log_reuse_wait_desc] [sysname] NOT NULL ) USE [DBAdata_archive] GO drop table tbl_Large_logfile_sync_with_recovery_model_check CREATE TABLE [dbo].[tbl_Large_logfile_sync_with_recovery_model_check]( [servername] [sysname] NULL, [dbname] [sysname] NOT NULL, [filename] [sysname] NOT NULL, [recovery_model] [sysname] NOT NULL, [log_size] [int] NULL, [freespace] [varchar](50) NULL, [Drive_letter] [sysname] NULL, [log_reuse_wait_desc] [sysname] NOT NULL, upload_date datetime ) SP: USE [DBAdata] GO SET ANSI_NULLS ON GO SET…
-
Analysis database recovery model alert notification Automon DBA SPs – AM18
This script will check the recovery model of databases based on production and non-production policy and notify an alert. SP: SP1 USE [DBAdata] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* USE [DBAdata] GO drop table tbl_recovery_model_non_Prod CREATE TABLE [dbo].[tbl_recovery_model_non_Prod]( [SERVER_NAME] [sysname] NOT NULL, [DB_NAME] [sysname] NOT NULL, [Recovery] [varchar](10) NULL ) USE [DBAdata_archive] GO CREATE TABLE [dbo].[tbl_recovery_model_non_Prod]( [SERVER_NAME] [sysname] NOT NULL, [DB_NAME] [sysname] NOT NULL, [Recovery] [varchar](10) NULL, [CREATE_DATE] [datetime] NULL ) select * from tbl_Error_handling order by Upload_Date desc select * from tbl_recovery_model_non_Prod and name not in (''''''''ReportServer'''''''',''''''''ReportServerTempDB'''''''') */ alter proc [dbo].[USP_recovery_model_Non_prod] /* Summary: Check the recovery mode for non prod Contact: Muthukkumaran Kaliyamoorthy SQL DBA…