There are times when you’re on vacation, kids soccer games, family outing, church, etc where you’re inaccessible. Thanks to Murphy’s law something bad will happen to the database server and alerts will fire off and you won’t receive those notifications because you’re enjoying life for once. This is where you want to enable Fail-Safe– a.k.a. your backup buddy.

As TechNet puts it, “when things go wrong with SQL Server notifications, operators are not notified and problems might not be corrected in a timely manner. As a precaution, you might want to designate a fail-safe operator. A fail-safe operator is notified when a SQL Server Agent cannot access system tables in the msdb database (which is where operator definitions and notification lists are stored), when all pager notifications to designated operators have failed, or when the designated operators are off duty (as defined in the pager schedule). ”

The steps to enable this tucked away feature is as follows.

Open up Management Studio -> Right Click on SQL Server Agent -> Click on Properties

Click on “Alert System”

Tick the “Enable mail profile” as you’ll need this for the Fail-Safe to work.

I’m assuming you already have a Mail Profile setup so at this point, select your Mail System/Profile from the drop-down-list. Once this is done, the Fail-Safe operator will highlight.

Tick on “Enable fail-safe operator” and select your Operator from the drop-down-list. Then Tick on how you want that operator to receive the messages and then Click OK

As always, I hope this helps.


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s