Today, I want to show how to be alerted any time one of your clustered SQL 2000 server fails over. This will work whether some admin fails it over on purpose, or accidentally. Of course, you will have to already have xp_sendmail 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.)
— For SQL 2000 clusters…
declare
@str nvarchar(500),
@machine varchar(100),
@body1 varchar(500) ,
@body2 varchar(500) ,
@sqlInstanceName varchar(100),
@SqlServer varchar(100)
set @machine = Convert(varchar(200), serverproperty(‘machinename’) )
set @SqlInstanceName = Convert(varchar(200), serverproperty(‘InstanceName’))
set @SqlServer = Convert(varchar(200), serverproperty(‘servername’))
set @body1 = ‘Sql Agent has restarted and ‘ + @sqlInstanceName +‘ is operating on ‘ + @machine
set @body2 = @sqlServer + ‘: Sql Agent restarted: ‘ + @sqlInstanceName +‘ is operating on ‘ + @machine
EXEC master.dbo.xp_sendmail
@recipients = ‘youremail@where.net’ ,
@message = @body2,
@copy_recipients = ‘AnybodyElse@where.net”,
@subject = @body1