SQL Server Database Mail Setup for Job Notifications

A backup job that failed at 02:00 and told nobody is worse than no job, because everyone assumes it worked. I found that out the hard way, so Database Mail is the first thing I set up on a new server. It sends through SMTP, runs in an external process (DatabaseMail.exe) so it doesn't tie up the engine, and it's in every edition except Express.

I do it all in T-SQL. The SSMS wizard is fine once, but it's no help when you repeat it on ten instances. You'll need an SMTP server that accepts mail from the host (an internal Exchange relay, Postfix, or an authenticated Office 365 account on smtp.office365.com, port 587, TLS), a running Agent, and sysadmin (or DatabaseMailUserRole in msdb for sending).

Turn it on

The Database Mail extended stored procedures are disabled by default.

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

Account and profile

An account holds the SMTP settings; a profile is a named group of accounts that senders refer to. One profile with one account is enough for most servers.

USE msdb;

EXEC dbo.sysmail_add_account_sp
    @account_name = 'SQLPROD01 Mail',
    @description = 'Outgoing mail for SQL Agent notifications',
    @email_address = 'sqlprod01@example.com',
    @display_name = 'SQLPROD01',
    @replyto_address = 'dba@example.com',
    @mailserver_name = 'smtp.example.com',
    @port = 25,
    @enable_ssl = 0;

EXEC dbo.sysmail_add_profile_sp
    @profile_name = 'DBA Notifications',
    @description = 'Default profile for Agent alerts';

EXEC dbo.sysmail_add_profileaccount_sp
    @profile_name = 'DBA Notifications',
    @account_name = 'SQLPROD01 Mail',
    @sequence_number = 1;

EXEC dbo.sysmail_add_principalprofile_sp
    @profile_name = 'DBA Notifications',
    @principal_name = 'public',
    @is_default = 1;

For an authenticated relay add @username and @password to sysmail_add_account_sp, set @port = 587 and @enable_ssl = 1. Making the profile public and default means sp_send_dbmail works without specifying @profile_name.

Send a test message

EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'dba@example.com',
    @subject = 'Database Mail test from SQLPROD01',
    @body = 'If you can read this, Database Mail works.';

The procedure returns immediately with "Mail (Id: 1) queued." Delivery happens asynchronously. Check the result:

SELECT mailitem_id, recipients, subject, sent_status, sent_date
FROM msdb.dbo.sysmail_allitems ORDER BY mailitem_id DESC;

SELECT log_date, description FROM msdb.dbo.sysmail_event_log ORDER BY log_date DESC;

sent_status should move from unsent to sent within a few seconds.

Operator

An operator is only a name and an address that jobs and alerts refer to. I always point it at a distribution list, never a person, because people change jobs and the mail keeps going to nobody.

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

Point the Agent at the profile

Everybody forgets this one, me included. The Agent has its own mail setting and it is off by default. In SSMS: right-click SQL Server Agent → Properties → Alert System, tick Enable mail profile, choose Mail system Database Mail and the profile DBA Notifications. Then restart the SQL Server Agent service; the setting is read only at startup.

The same setting can be written through the registry-backed procedure the GUI uses:

EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder = 1;
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 Notifications';

Attach notifications to jobs

For each job, on the Notifications page pick E-mail → DBA Team → When the job fails. In T-SQL, for all existing jobs at once:

DECLARE @job_id uniqueidentifier, @sql nvarchar(max) = N'';
SELECT @sql += N'EXEC msdb.dbo.sp_update_job @job_id = ''' + CAST(job_id AS nvarchar(36)) + N''', @notify_level_email = 2, @notify_email_operator_name = N''DBA Team'';' + CHAR(13)
FROM msdb.dbo.sysjobs WHERE enabled = 1;
EXEC sp_executesql @sql;

@notify_level_email = 2 means "on failure"; 1 is on success, 3 is always.

Prove it with a fake failure

Create a throwaway job with a single step RAISERROR('forced failure', 16, 1), run it, and confirm the failure mail arrives with the job name and step message in the body. Then delete the job.

If no mail arrives

• "The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 ... Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated.)" in sysmail_event_log. The relay wants authentication or TLS; add credentials and set @enable_ssl = 1, port 587.
• "Mail queued" but sent_status stays unsent forever. The external DatabaseMail.exe process is not starting. Check EXEC msdb.dbo.sysmail_help_status_sp and start it with EXEC msdb.dbo.sysmail_start_sp. On some servers the SQL Server service account lacks permission to %ProgramFiles%\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\DatabaseMail.exe.
• Jobs fail but no mail arrives, while sp_send_dbmail works. The Agent mail profile is not enabled or the Agent was not restarted after enabling it. This is the case nine times out of ten.
• "profile name is not valid" from sp_send_dbmail run by a non-sysadmin. The profile is private; either make it public as above or grant the user DatabaseMailUserRole and a private profile mapping.

Next on my list is Agent alerts for severity 17 to 25 and errors 823/824/825, all to the same operator.