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…
-
New database user creation alert notification Automon DBA SPs – AM16
This script will check the new database and user creation and notify an alert, if it is tempdb then service restarted. Table: use dbadata go go drop table DBA_NEW_OBJECT_LOGIN_LIST go CREATE TABLE [dbo].[DBA_NEW_OBJECT_LOGIN_LIST]( [SERVER_NAME] [varchar](100), [NAME] [sysname] NOT NULL, [CREATE_DATE] [datetime] NULL, [TYPE] [varchar](10) NULL ) use dbadata_archive go drop table DBA_NEW_OBJECT_LOGIN_LIST go CREATE TABLE [dbo].[DBA_NEW_OBJECT_LOGIN_LIST]( [SERVER_NAME] [varchar](100), [NAME] [sysname] NOT NULL, [CREATE_DATE] [datetime] NULL, [TYPE] [varchar](10) NULL, [Upload_date] [varchar](10) NULL ) SP: USE [DBAdata] GO /****** Object: StoredProcedure [dbo].[USP_DBA_GETNEW_DB_AND_LOGIN] Script Date: 9/27/2013 7:44:10 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* use dbadata go go drop table DBA_NEW_OBJECT_LOGIN_LIST go CREATE TABLE [dbo].[DBA_NEW_OBJECT_LOGIN_LIST]( [SERVER_NAME] [varchar](100), [NAME]…