Database Mail Agent Notifications

What this check looks for

The check reads the SQL Server Agent error log and looks for the entries Agent writes at startup about its mail session. When Agent starts without a usable Database Mail profile it records that fact, and that entry is what this finding reports.

It is skipped on Express Edition, which has no SQL Server Agent.

Why it matters

Every other notification feature on the instance depends on this one setting, and none of them tell you when it is missing.

A job can be configured to email an operator on failure. An alert can be configured to email on severity 19 and above. Both will look correct in the user interface, both will record in their history that a notification was attempted, and if Agent has no mail profile neither will send anything. The failure is silent at both ends: nothing arrives, and nothing obvious says why.

That produces the worst pattern in monitoring, which is believing you are covered when you are not. A backup job that has been failing for three weeks with a notification configured is worse than one with no notification at all, because in the second case somebody was still checking.

Three separate things have to be true for a job failure to reach a person, and this check covers the one in the middle:

1. Database Mail is configured with a working profile and account.
2. SQL Server Agent is told to use that profile, and Agent has been restarted since.
3. The job or alert names an operator with a valid email address.

The restart is the step most often missed. Setting the Agent mail profile does not take effect until the Agent service restarts. It is common to find the setting correct, the profile valid, and Agent still running with no mail session because nobody bounced it after the change.

How to confirm it yourself

What Agent believes about mail right now:

EXEC msdb.dbo.sp_get_sqlagent_properties;

Look at email_save_in_sent_folder_flag and the profile columns. A NULL or empty profile name is the finding.

The registry values Agent actually reads, which is the authoritative version:

DECLARE @mailProfile NVARCHAR(255), @useMail INT;

EXEC master.dbo.xp_instance_regread
     N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
     N'UseDatabaseMail', @useMail OUTPUT;

EXEC master.dbo.xp_instance_regread
     N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
     N'DatabaseMailProfile', @mailProfile OUTPUT;

SELECT @useMail AS [use_database_mail], @mailProfile AS [agent_mail_profile];

use_database_mail of 1 and a named profile is what correct looks like.

Whether Database Mail itself is even enabled:

SELECT [name], [value_in_use]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] IN ('Database Mail XPs', 'Agent XPs');

The profiles that exist:

SELECT p.[profile_id], p.[name] AS [profile_name], a.[name] AS [account_name],
       a.[email_address], s.[servername] AS [smtp_server], s.[port]
  FROM msdb.dbo.sysmail_profile      AS p
  LEFT JOIN msdb.dbo.sysmail_profileaccount AS pa ON pa.[profile_id] = p.[profile_id]
  LEFT JOIN msdb.dbo.sysmail_account AS a  ON a.[account_id]  = pa.[account_id]
  LEFT JOIN msdb.dbo.sysmail_server  AS s  ON s.[account_id]  = a.[account_id];

And whether mail is actually being delivered, which is the question behind all of this:

SELECT TOP (50) [sent_status], [send_request_date], [recipients], [subject]
  FROM msdb.dbo.sysmail_allitems
 ORDER BY [mailitem_id] DESC;

SELECT TOP (20) [log_date], [description]
  FROM msdb.dbo.sysmail_event_log
 ORDER BY [log_id] DESC;

A long run of failed in sent_status means the profile exists and the SMTP relay is rejecting it, which is a different problem with the same symptom.

How to fix it

Set the Agent mail profile, then restart Agent. The restart is not optional.

If Database Mail is not enabled at all:

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

Create an account and a profile if none exists:

EXEC msdb.dbo.sysmail_add_account_sp
     @account_name       = 'SQLAlerts',
     @email_address      = '[email protected]',
     @display_name       = 'SQL Server Alerts',
     @mailserver_name    = 'smtp.yourcompany.com',
     @port               = 587,
     @enable_ssl         = 1;

EXEC msdb.dbo.sysmail_add_profile_sp
     @profile_name = 'SQLAlertsProfile';

EXEC msdb.dbo.sysmail_add_profileaccount_sp
     @profile_name = 'SQLAlertsProfile',
     @account_name = 'SQLAlerts',
     @sequence_number = 1;

Test it before wiring Agent to it:

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'SQLAlertsProfile',
     @recipients   = '[email protected]',
     @subject      = 'Database Mail test',
     @body         = 'If this arrives, the profile works.';

Then point Agent at it:

EXEC msdb.dbo.sp_set_sqlagent_properties
     @email_save_in_sent_folder = 1,
     @databasemail_profile      = N'SQLAlertsProfile',
     @use_databasemail          = 1;

Restart the SQL Server Agent service. Until you do, Agent is still running with whatever mail session it started with.

Then finish the chain, because the profile alone notifies nobody:

-- an operator to send to
EXEC msdb.dbo.sp_add_operator
     @name = 'DBA Team', @enabled = 1,
     @email_address = '[email protected]';

-- a failsafe, for when the alerting path itself is broken
EXEC msdb.dbo.sp_MSsetalertinfo @failsafeoperator = 'DBA Team', @notificationmethod = 1;

-- and tell a job to use it
EXEC msdb.dbo.sp_update_job
     @job_name = 'Nightly Full Backup',
     @notify_level_email = 2,          -- on failure
     @notify_email_operator_name = 'DBA Team';

Use a distribution list, not a person. An operator pointed at an individual’s mailbox stops working the day they leave, and nobody notices because the failure is silent again.

Then prove it end to end. Create a job with a step that runs RAISERROR('test', 16, 1), set it to notify on failure, run it, and confirm the mail arrives. Configuration that has not been tested this way is configuration you are hoping about.

How long it takes

About an hour, most of it obtaining SMTP relay details and testing delivery. The Agent restart is the only interruption, and it affects running jobs only.

Related reports

Related checks

Frequently asked questions

Database Mail works when I test it, so why is this reported? sp_send_dbmail uses the profile you name. Agent uses the profile recorded in its own configuration, which is a separate setting, and it reads it at service start.

Do I really have to restart Agent? Yes. The mail session is established when the service starts, and a profile change made while it runs does not take effect until it restarts.

We use a third party monitoring tool, so do we need this? If that tool watches job outcomes independently, Agent mail is a backup path rather than the primary one. It is still worth having, because it is the path that works when the monitoring agent itself is the thing that stopped.

Express Edition reports nothing here. Express has no SQL Server Agent, so the check is skipped. Scheduled work on Express has to be driven from outside the instance.