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…
-
No backup for database alert notification Automon DBA SPs – AM17
This script will check the last backup taken and notify an alert when there is no backup of given threshold. Table: -- drop table tbl_no_full_backup_7days use dbadata drop table tbl_no_full_backup_7days CREATE TABLE [dbo].[tbl_no_full_backup_7days]( serverName varchar(50),databaseName Varchar(100),type varchar(1),last_backup_date datetime, DB_Status sql_variant ) use dbadata_archive go drop table tbl_no_full_backup_7days CREATE TABLE [dbo].[tbl_no_full_backup_7days]( serverName varchar(50),databaseName Varchar(100),type varchar(1),last_backup_date datetime, DB_Status sql_variant,upload_date datetime ) SP: USE [DBAdata] GO /****** Object: StoredProcedure [dbo].[USP_DBA_No_full_backup] Script Date: 7/6/2015 3:32:45 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* -- drop table tbl_no_full_backup_7days use dbadata drop table tbl_no_full_backup_7days CREATE TABLE [dbo].[tbl_no_full_backup_7days]( serverName varchar(50),databaseName Varchar(100),type varchar(1),last_backup_date datetime, DB_Status sql_variant ) use dbadata_archive go drop table tbl_no_full_backup_7days…