AutoMon

Automon auto maintenance mode alert notification Automon DBA SPs – AM22

Advertisements

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 (@count_s>=5)
begin


 DECLARE @server_name SYSNAME
      DECLARE @DESC SYSNAME
      DECLARE @sql varchar(8000)
      DECLARE @minrow int
      DECLARE @maxrow int

 declare @ping_u table (id int  primary key identity, 
 servername varchar(100),count_R varchar(100)) 
 
insert into @ping_u

select Server_name,count(*) from tbl_Error_handling 
where Module_name='ping' and  Upload_Date>=DATEADD(HH,-1,getdate())
group by Server_name
having count(*)>=5
 
SELECT @minrow = MIN(id)FROM   @ping_u
SELECT @maxrow  = MAX(id) FROM   @ping_u
 
 while (@minrow <=@maxrow)
 begin
 
 select @Server_name=Servername  from @ping_u where ID = @minrow 
 
 --select @minrow,@maxrow

print @Server_name


update DBA_All_servers set SVR_status='not ping_U' where Description=@Server_name
update DBA_All_servers set maintenance_date= getdate() where Description=@Server_name
--select Description,SVR_status from DBA_All_servers where Description=@Server_name

 set @minrow =@minrow +1 
 end

end

END

 

 

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: