Alerts that go nowhere: configuring Database Mail and a fail-safe operator for SQL Server Agent

SQL Server Agent sends notifications to operators. If the operator for an alert can't be notified, for example because it's off duty under its pager schedule, disabled, or missing an address, Agent sends the notification to the fail-safe operator instead. With no fail-safe operator, the notification is simply lost.

Even more common: Database Mail works, but SQL Agent was never told to use it. Jobs then "notify" the operator and nothing arrives.

#Step 1: configure Database Mail

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Database Mail XPs', 1;   RECONFIGURE;

EXEC msdb.dbo.sysmail_add_account_sp
     @account_name = N'DBA SMTP', @email_address = N'sql01@corp.example.com', @display_name = N'SQL01 SQL Server',
     @mailserver_name = N'smtp-relay.corp.example.com', @port = 25, @enable_ssl = 0;
EXEC msdb.dbo.sysmail_add_profile_sp @profile_name = N'DBA';
EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name = N'DBA', @account_name = N'DBA SMTP', @sequence_number = 1;

-- test the profile on its own
EXEC msdb.dbo.sp_send_dbmail @profile_name = N'DBA', @recipients = N'dba-team@corp.example.com',
     @subject = N'Database Mail test from SQL01', @body = N'If you can read this, Database Mail works.';

Use your organisation's SMTP relay. Port, TLS and authentication depend on it (for example 587 with @enable_ssl = 1 and @username/@password for an authenticated relay).

#Step 2: tell SQL Server Agent to use Database Mail

In SSMS, go to SQL Server Agent → Properties → Alert System. Tick Enable mail profile, choose Database Mail and the DBA profile. The script equivalent writes the same Agent settings:

EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1;
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'DBA';

These are the calls SSMS generates when you script the dialog. xp_instance_regwrite maps the path to the correct instance. Restart the SQL Server Agent service for the mail settings to take effect.

#Step 3: create operators (teams, not people)

EXEC msdb.dbo.sp_add_operator @name = N'DBA Team', @enabled = 1, @email_address = N'dba-team@corp.example.com';
EXEC msdb.dbo.sp_add_operator @name = N'DBA On-call', @enabled = 1, @email_address = N'dba-oncall@corp.example.com';

Use distribution lists or team mailboxes. When people change, you update the list, not every SQL Server.

#Step 4: set the fail-safe operator

In SSMS, Agent Properties → Alert System → Fail-safe operator: tick Enable fail-safe operator, choose the operator and tick E-mail.

Scripted:

EXEC master.dbo.sp_MSsetalertinfo @failsafeoperator = N'DBA On-call', @notificationmethod = 1;   -- 1 = e-mail

sp_MSsetalertinfo is undocumented, but it's what SSMS uses for this dialog. To check the current setting:

DECLARE @op nvarchar(128), @method int;
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeOperator', @op OUTPUT;
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertNotificationMethod', @method OUTPUT;
SELECT @op AS failsafe_operator, @method AS notification_method;

#Step 5: attach notifications to jobs and alerts

-- every job: e-mail the DBA Team on failure
EXEC msdb.dbo.sp_update_job @job_name = N'DBA - Backup - User databases FULL',
     @notify_level_email = 2, @notify_email_operator_name = N'DBA Team';

-- critical alerts
EXEC msdb.dbo.sp_add_alert @name = N'Sev 19-25 / 823-825',  @severity = 19, @include_event_description_in = 1;
EXEC msdb.dbo.sp_add_notification @alert_name = N'Sev 19-25 / 823-825', @operator_name = N'DBA On-call', @notification_method = 1;

Create one alert per severity (19 to 25) and per message (823, 824, 825), each with a notification. Set @delay_between_responses (for example 300 seconds) so a flood of errors doesn't flood the mailbox.

#Step 6: test the whole chain

-- 1. a job that fails on purpose
EXEC msdb.dbo.sp_add_job @job_name = N'DBA - Test failure notification', @notify_level_email = 2, @notify_email_operator_name = N'DBA Team';
EXEC msdb.dbo.sp_add_jobstep @job_name = N'DBA - Test failure notification', @step_name = N'Fail', @command = N'RAISERROR(''Test failure'', 16, 1);';
EXEC msdb.dbo.sp_add_jobserver @job_name = N'DBA - Test failure notification';
EXEC msdb.dbo.sp_start_job @job_name = N'DBA - Test failure notification';

-- 2. did mail go out?
SELECT TOP 10 mailitem_id, recipients, subject, sent_status, sent_date FROM msdb.dbo.sysmail_allitems ORDER BY mailitem_id DESC;
SELECT TOP 10 log_date, description FROM msdb.dbo.sysmail_event_log ORDER BY log_id DESC;

Delete the test job afterwards.

#Troubleshooting

Tip

Add the Step 6 test to your post-build and post-patching checklist. Mail is the one dependency nobody notices is broken until the night it's needed.