Enabling a Fail Safe Operator in SQL Server Agent

By:   |   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.

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.



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ken Simmons 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



Comments For This Article

















get free sql tips
agree to terms