» Get Alerted when your 2005 SQL cluster fails over

Today, I want to show how to be alerted any time one of your clustered SQL 2005 server fails over.  This will work whether some admin fails it over on purpose, or accidentally.

Of course, you will have to already have DBmail enabled.  The key to making this work is to have a job which is scheduled not at a set time but, instead, for whenever the SQL Server Agent restarts.  (This will mean that if someone restarts the agent only, you will be alerted for that as well.)

Notice that “as a bonus” i also include the last 15 lines of the SQL error log.  This allows you to see what likely caused the failover…

Enjoy:

–code follows:

set nocount on

go

declare @str nvarchar(500),@machine varchar(50), @body1 varchar(200)

set @body1 = Convert(varchar(200), serverproperty(‘machinename’) )

set @machine = Convert(varchar(200), serverproperty(‘computerNamePhysicalNetBios’))

set @body1 = ‘Sql Agent has restarted and ‘ + @body1 + ‘ is operating on ‘ + @machine

set @str =’DECLARE @oem_errorlog nvarchar(255)

create table #LogDetails(logdate datetime, processinfo varchar(55), Last15MessageText varchar(2000))

insert #LogDetails exec master.dbo.xp_readerrorlog 1

select top 15 logdate, Last15MessageText from #logdetails order by logdate desc

drop table #logdetails’;

–exec sp_executesql @str

select @machine = ‘Sql Agent restarted on ‘ + @machine

exec msdb.dbo.sp_send_dbmail @profile_name = ‘FrdSqlPrd01Mail’

,@recipients = ‘stephend@coj.net’

,@subject = @machine

,@importance = ‘High’

,@body = @body1

,@query = @str

,@query_result_header = 0

 

–Code ends

Leave a Reply

Your email address will not be published. Required fields are marked *