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.