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