Wednesday, 4 July 2012

SQL SERVER:How Can I Get Notification Alert When Fail Over Happened ?

It was long time I was searching for simple solution to get notification alert in my inbox when ever fail over happened in the cluster environment. SCOM will help us to do that , but I do not have direct control over it and the mail first goes to the Infrastructure support team and they forward the alert to the respective team.

Let us see how  DBA's can schedule an email alert with out depending on the SCOM or any other third party tool. When  fail over happen, both SQL server and Agent service will get restarted.In sql server job scheduler, there is an option to schedule a job when SQL server agent starts. For that we have to select the schedule type as "Start Automatically when sql server agent start".

To get an alert when a fail over happened, create a job with following code in the job step

DECLARE @importance AS VARCHAR(6)
DECLARE @recipientsList VARCHAR(100)SELECT  @recipientsList =','
SELECT  @NodeName = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(200))
SELECT  @Subject  = 'Fail over Happened for Instance '+@InstanceName
SELECT  @body = 'Fail over Happened for Instance '+@InstanceName + '. This instance is currently running on the node '+@NodeName
SELECT  @importance ='High' 
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='MyMailProfile',
@subject = @subject ,
@body = @body,
@body_format = 'HTML' ,

In the schedule pane select schedule Type as "Start Automatically when sql server agent start".

Do this on all instances of the cluster and it is ready . Wait for next fail over. Mail will be there in your inbox.Please be careful that you will get a mail even if the instance got restarted in the same instance or you just stopped and started the agent service. But that can be easily ignored or can be avoid by tweaking the above code little bit.

If you liked this post, do like my page on FaceBook at


  1. This is not a bad idea at all. I think the script really just notifies you if SQL Server has been restarted rather than failed over since the restart can be on the same node, but still this is something I want to know has happened: failover cluster or standalone instance. Good idea.
    -- Mark D Powell --

    1. Yes when sql server restart or you just stop and start , this will be fired . but if you are restarting it manually , you can just ignore it.I didn't find any other simple solution .

  2. Well you can simply save the current node name in a table somewhere. Then modify the script to compare nodes, & only send the email if there is a difference.

    1. That looks good..but there will be scenarios where SQL server will just stop and start on the same node . Especially in case of scheduler deadlock and all. If sql server got restarted in the same node , we will not get the alert.


  3. I loved the way you discuss the topic great work thanks for the share, Let me share this, dotnet training in pune


  4. شركة نقل اثاث بالدمام التفاؤل شركة نقل اثاث بالخبر كما انها افضل شركة نقل اثاث بالجبيل نقل عفش واثاث بالجبيل والخبر والقطيف والدمام
    شركة نقل اثاث بالدمام
    شركة نقل اثاث بالجبيل

  5. شركة نقل عفش واثاث بالدمام ابيات الشرقيه لخدمات نقل العفش والاثاث بالدمام
    شركة نقل عفش بالدمام
    نقل عفش بالخبر
    شركة نقل اثاث الدمام
    نقل عفش الدمام
    نقل عفش بالدمام
    ان اردت نقل عفش منزلك بالدمام ابيات الشرقية من اهم شركات نقل العفش بالدمام والخبر والجبيل والقطيف والاحساء

  6. شركة نقل عفش بالرياض شركة نقل عفش بالطائف شركة نقل عفش بالدمام شركة نقل عفش بجدة شركة نقل عفش بالمدينة المنورة