Auto notification for failover of a server and restart of SQL Server services

By:   |   Comments (7)   |   Related: > SQL Server Agent

Our clustered Microsoft SQL Server environment hosts over 200 databases currently across all nodes.  Whereas clustering technology has been around for quite some time now, many applications still are not cluster-aware.  What does it mean to be a "cluster-aware" application?  Simply-put, it means that the application, upon failover of a database in a clustered instance, will attempt to re-connect to the database and re-establish the client connections that were instantiated prior to the failover of the database.  The problem we encountered was with a single application, provided and monitored by an outside company for the sake of tracking our heating and cooling systems' performance for temperature differentials.  This application is not cluster-aware and would fail whenever a failover occurred.  Since this solution is important, but not five-9's critical, we simply need to notify the vendor when a failover occurs.  While we could employ Microsoft System Center or Microsoft Operations Manager to alert when this event does occur, I would have had to enlist resources across multiple teams to get this accomplished under that plan.

The solution I employed was to simply create a job on the clustered SQL Server instance that will send an email via Database Mail to the vendor support email address, copying the Database Administrator on-call, and the Analyst on-call in the email.  I then assigned the job a schedule of firing whenever services start up.  This way every time the SQL Agent service starts an email will be sent notifying us that a restart of the service has occurred.

For those of you who are not familiar with the failover process of Microsoft SQL Server, the services are associated with resource groups located on a shared server volume (SAN/NAS).  The resource group is owned at any given time by a single physical server, referred to as a node in clustering terminology.  If a node experiences a shutdown request the resource group ownership is transferred from the failing node to another available node in the cluster.  This does involve cycling of the SQL Server services during this process and therefore would trigger the execution of this SQL Server Agent job when the services are restarted.  It would also occur if services are restarted for any reason, additional to a planned or emergent failover process, but that is appropriate for our needs in this case as well.

The next few screen shots will walk you through the process of setting this up via Microsoft SQL Server Management Studio.  You may choose to perform these tasks via T-SQL coding, but I find the job creation process works best from the SQL Server Management Studio GUI, then scripting the job from the Script button if you wish to later run this process on additional SQL Server instances.  At the end of this article I will outline some requirements for this process to work successfully in your environment.  By now, most of you have created a SQL Server Agent job; the purpose of this tip is to present how to handle remote notification of a SQL Server instance failover on a clustered environment, job creation is merely the process that has worked best for me and was simplest to implement.  Not too much heavy lifting in this tip - I promise.

SQL Server Management Studio Process

From within SQL Server Management Studio connect to the clustered instance you intend to configure, navigate to SQL Server Agent/Jobs, right click and select New Job... from the available pop-up menu as shown below.

2009 01 01

Enter in values for Job Name, Owner, Category, and Description parameters. 

2009 01 02

Navigate to the Steps page in the SQL Agent Job wizard and create a new Step.  Enter in the parameter values shown below (or values that conform to your environment.)  You will particularly need to enter correct coding for the Command parameter in this step.  My recommendation is to always test the command text within the context of a SQL Server Management Studio query before ever implementing it from within a SQL Server Agent job.  You will need to enter correct command parameter values (from your environment) for @profile_name, @recipients, @copy_recipients, @subject and @body for the call to dbo.sp_send_dbmail in the job command.  Information concerning SQL Server Database Mail and its associated stored procedures can be found in this 2008 tip on

2009 01 03

Proceed onto the Advanced page of the Job Step form and enter the values below, substituting a Output file location that works best for your needs.  This may not be a function that you frequently use (the default is a null value) but I rely on this output file providing me with more-detailed logging than what is typically afforded via the default logging process of the SQL Server Agent.  This process was previously outlined on MSSQLTips here.

2009 01 04

Click OK to save and close out of the Job Step form.  This returns you back to main Job creation forms for SQL Server Agent.  Navigate to the Job Schedule form and enter values identical to those presented below.  The values may seem inconsistent for Schedule type and Summary/Description, but the value for Description is read-only, and is unable to be modified.  Disregard it in this case.  This is the most-important screen.  It is the main reason that this process works for the purpose at-hand.  After making sure your command code is sound, making sure the job fires upon service start-up is critical.

2009 01 05

Once this form is complete, save (or script) the job creation and you should be done.  I suggest first setting this up on a development or test cluster and test failover and determine if the job behaves as expected before you implement in production.

Now, there are some prerequisites that need to be in place before this process will work.  Those include:

Next Steps

  • Configure SQL Server Database Mail, if not currently configured and enabled.
  • Modify the command code to conform to your particular needs.  Test the command code outside of the context of a job from within SQL Server Management Studio
  • Create the Agent job as instructed above on a TEST or DEVELOPMENT SQL Server cluster if available in your domain; test failover for expected results.
  • Follow identical process in your PRODUCTION SQL Server cluster once testing results meet your standards for performance.
  • Download the script

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, April 8, 2016 - 12:36:38 AM - Ron Back To Top (41161)

 Thank you very much.  Your script and solution worked well.  Thank you for providing it. 


Thursday, August 30, 2012 - 2:52:05 PM - SQLScottGleason Back To Top (19328)

Correction to the reply above, you should use


SET @ServerNodeName = CONVERT(sysname, SERVERPROPERTY('MachineName'ComputerNamePhysicalNetBIOS'))



Thursday, August 30, 2012 - 1:44:56 PM - SQLScottGleason Back To Top (19326)

/* There is a code template (works in 2005/2008/2008RS) that will help determine what node SQL is running on and send out an email alert if necessary */

DECLARE @ServerNodeName varchar(MAX);
SET @ServerNodeName = CONVERT(sysname, SERVERPROPERTY('MachineName'))

IF (@ServerNodeName = '')
  print 'SQL is running on the correct node; no action necessary';
  PRINT 'SQL Node has Failed over and is runnign on the incorrect node named ' + @ServerNodeName
  /* Write code here to do something; like sending out an email alert */

Thursday, March 1, 2012 - 6:20:56 AM - bojanna mk Back To Top (16226)


 How can we know weather it was jusy a restart of the server or the instance was failoverd?Because in either case,we get this alert.

Tuesday, March 2, 2010 - 9:28:40 AM - yakaaithiri Back To Top (4991)

Is there a way to trigger a email when the SQL server agent service stops.

For restarting we can use this .

But if somebody stops the agent we need to be notified right ?

Please share if a have a way for it.


Monday, January 12, 2009 - 12:22:57 PM - timmer26 Back To Top (2541)

Very good.  You are absolutely correct and I like your workaround using creation date of tempdb since it is recreated upon restart.  I do not advocate using sp_procoption as I do not like to add extra processing upon a system restart.  Typically you want SQL to start as cleanly as possible.

Monday, January 12, 2009 - 11:19:27 AM - MohammedU Back To Top (2539)

The method you are using may give incorrect information becuase it will send the notification even in the case of SQL Agent restarted. I believe if you change your code to check the TEMPDB creation date is < a minute or two or the to check wheather it is SQL restart or agent restart might be better...




@profile_name = 'SQL Server Notification Service',

@recipients = '[email protected]',

@copy_recipients = '[email protected];[email protected]',

@body = 'This is an informational message only: SQL services possibly restarted on SQL01. Please restart any dependent application services after verifying status with DBA Team first.',

@subject = 'SQL Services Restarted on SQL01'



You can also create a procedure and set it to automatically Execute Stored Procedures When Starting SQL Server using sp_procoption procedure...


get free sql tips
agree to terms