This script will check the Automon server status, when the ping fails five times based on the run schedule, it will automatically place it on maintenance mode.   SP: -- exec usp_ping_server_morethan_5_fail_status_change -- select * from DBA_All_servers where SVR_status<>'running' -- select * from tbl_Error_handling where Module_name='ping' alter proc usp_ping_server_morethan_5_fail_status_change /* Summary: Auto maintenance after 5 ping fails Contact: Muthukkumaran Kaliyamoorthy SQL DBA Description: Auto maintenance after 5 ping fails ChangeLog: Date Coder Description 2013-jan-21 Muthukkumaran Kaliyamoorhty Updated the 2012 functionality */ as BEGIN declare @count_s int select @count_s=count(*) from tbl_Error_handling E join DBA_All_servers A on (e.Server_name=a.Description) where Module_name='ping' and Upload_Date>=DATEADD(HH,-1,getdate()) --and a.SVR_status='running' group by Server_name having count(*)>=5 --select @count_s if…

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…

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: /*…

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…

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…