• AutoMon

    Automon server report alert notification Automon DBA SPs – AM21

    This script will check the automon server status report and notify an alert.   SP: alter procedure USP_DBA_AutoMon_server_Report /* Summary: Send a server which are not monitored by tool Contact: Muthukkumaran Kaliyamoorthy SQL DBA Description: Send a server which are not monitored by tool ChangeLog: Date Coder Description 2013-jan-21 Muthukkumaran Kaliyamoorhty Updated the 2012 functionality */ --WITH ENCRYPTION AS BEGIN SET nocount ON --Send an email to DBA team ----------------------------------------------------------------- DECLARE @minid INT DECLARE @maxid INT DECLARE @servername varchar(100) DECLARE @SVR_status varchar(100) -- select * from dbadata.dbo.tbl_get_datafiles_size if exists (select Description,SVR_status from dbadata.dbo.DBA_All_servers where SVR_status<>'running') begin DECLARE Svr_running_CUR CURSOR FOR select Description,SVR_status from dbadata.dbo.DBA_All_servers where SVR_status<>'running' OPEN Svr_running_CUR FETCH NEXT…

  • AutoMon

    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,  DBA

    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  

  • AutoMon

    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…

  • AutoMon

    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…