Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Enabling a Fail Safe Operator in SQL Server Agent


By:   |   Read Comments   |   Related Tips: 1 | 2 | More > SQL Server Agent

Problem

I rely on Database Mail to send notifications for SQL Server Agent job failures. Is there a way to ensure that the mail will be delivered if something were to happen to the operators in msdb database?

Solution

You can enable a fail-safe operator that will receive all emails in the event that SQL Server Agent cannot communicate with the system tables in the msdb database. This is accomplished by recording the information about the fail-safe operator in the registry. The fail-safe operator will also receive notifications if you have scheduled operators to only receive notifications during certain time periods and a notification occurs outside of that range. For example, if no operators are set to receive notifications on Sunday, the notification will automatically go to the fail-safe operator.

To enable a fail-safe operator, right-click SQL Server Agent and select Properties from the context menu. Then select the Alert System page as shown in the following image.

right-click SQL Server Agent and select Properties from the context menu

Select the Enable fail-safe operator checkbox and then choose the appropriate operator and notification method. Click OK to save the changes.

Another nice feature of the fail-safe operator is that once you have designated a fail-safe operator, you cannot delete that operator until you remove the fail-safe operator from SQL Server Agent or assign another operator. For example, I will get the following error message if I try to delete the DBA operator used in this example.

 you cannot delete that operator until you remove the fail-safe operator from SQL Server Agent or assign another operator
Next Steps

There are some prerequisites to enabling a fail-safe operator such as setting up Database Mail, creating an Operator, and enabling Database Mail in SQL Server Agent. You can review the following links for more information on configuring those prerequisites.



Last Update:






About the author
MSSQLTips author Ken Simmons Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools