Enabling a Fail Safe Operator in SQL Server Agent

By: Ken Simmons   |   Updated: 2010-05-07   |   Comments   |   Related: 1 | 2 | > 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.

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.

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.

• Setting up Database Mail for SQL 2005
• SQL Server Database Mail and SQL Agent Mail setup by using a script
• How to setup SQL Server alerts and email operator notifications

About the author

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

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

View all my tips