• AutoMon

    Automon linked server working status report alert notification Automon DBA SPs – AM25

    This script will check the Automon linked server status and notify an alert, when it is not running.   SP: /* use [DBAdata] drop table tbl_Linked_Server_Status go CREATE TABLE [dbo].[tbl_Linked_Server_Status]( [servername] varchar(200), error_no bigint, errors varchar(2000), Date datetime ) */ -- select * from tbl_Linked_Server_Status use [DBAdata] go -- Exec [DBAdata].[dbo].[Usp_Linked_server_Status_Check] --DROP PROC [Usp_Linked_server_Status_Check] alter PROCEDURE [dbo].[Usp_Linked_server_Status_Check] /* Summary: Check the SQL agent stats Contact: Muthukkumaran Kaliyamoorhty SQL DBA Description: Check the SQL agent stats ChangeLog: Date Coder Description 2013-jan-21 Muthukkumaran Kaliyamoorhty Updated the 2012 functionality */ --WITH ENCRYPTION AS BEGIN SET nocount ON -- select * from dbadata.dbo.tbl_Linked_Server_Status Truncate table dbadata.dbo.tbl_Linked_Server_Status DECLARE @server_name SYSNAME DECLARE @DESC SYSNAME --DECLARE @sql…

  • AutoMon

    Agent SQL service status report alert notification Automon DBA SPs – AM24

    This script will check the SQL server agent service status and notify an alert, when it is not running.   SP: /* use [DBAdata] drop table tbl_agent_Status go CREATE TABLE [dbo].[tbl_agent_Status]( [servername] varchar(200) not null primary key, instance_name varchar(200), Edition varchar (50), Status varchar (50), Date datetime, ) */ -- select * from tbl_agent_Status use [DBAdata] go -- Exec [DBAdata].[dbo].[Usp_Agent_Status] --DROP PROC [Usp_Agent_Status] alter PROCEDURE [dbo].[Usp_Agent_Status] /* Summary: Check the SQL agent stats Contact: Muthukkumaran Kaliyamoorhty SQL DBA Description: Check the SQL agent stats ChangeLog: Date Coder Description 2013-jan-21 Muthukkumaran Kaliyamoorhty Updated the 2012 functionality */ --WITH ENCRYPTION AS BEGIN SET nocount ON -- select * from dbadata.dbo.tbl_agent_Status Truncate table…

  • AutoMon

    Automon server error report alert notification Automon DBA SPs – AM23

    This script will check last day server error report and notify an alert.   SP: alter procedure USP_DBA_ErrorHandling_Report /* Summary: Send a error report of script Contact: Muthukkumaran Kaliyamoorthy SQL DBA Description: Send a error report of script 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 @Module_name varchar(100) DECLARE @error varchar(100) -- select * from dbadata.dbo.tbl_get_datafiles_size if exists ( select 1 from tbl_Error_handling where Upload_Date>=DATEADD(Day,-1,getdate()) and Module_name <>'Perfmon' group by Server_name,Module_name,[error_message] having count(*)>=1 ) begin DECLARE Svr_error_CUR CURSOR FOR select Server_name,Module_name, left([error_message],25)…

  • AutoMon

    Automon auto maintenance mode alert notification Automon DBA SPs – AM22

    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…