Enabling a Fail Safe Operator in SQL Server Agent


By:   |   Updated: 2010-05-07   |   Comments   |   Related: 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 Updated: 2010-05-07


get scripts

next tip button



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





Comments For This Article





download





Recommended Reading

SQL Server Agent Jobs without an Operator

Running a SSIS Package from SQL Server Agent Using a Proxy Account

Querying SQL Server Agent Job Information

Querying SQL Server Agent Job History Data

SQL Server Agent Job Schedule Reporting








get free sql tips
agree to terms