Quick Scan Report – Failed Database Mail

What this check looks for

Messages in msdb.dbo.sysmail_allitems with a sent_status of failed or retrying in the last day, counted and reported with the time of the most recent failure.

This page also covers issue 104, Older Failed Database Mail, which is the same condition found further back in the history.

The check requires sysadmin and is skipped on Amazon RDS.

Why it matters

Database Mail is the delivery mechanism for everything the instance would tell you. When it fails, the instance keeps generating the notifications and none of them arrive:

• Alerts on severity 19 through 25 and on corruption errors 823, 824 and 825.
• Job failure notifications, including backups and integrity checks.
• Anything your own jobs send with sp_send_dbmail.

The failure mode is the same one that runs through this whole category: silence is indistinguishable from health. An operator who stops receiving failure emails concludes that nothing is failing. The instance concludes it has told somebody. Both are wrong, and nothing reconciles them until a scan like this one looks at the queue.

retrying deserves as much attention as failed. A message that is retrying has not been delivered yet and may never be. On a mail server that is refusing connections, messages accumulate in this state indefinitely, and the notification about last night’s failed backup is sitting in a queue rather than in an inbox.

There is a secondary consequence: every failed and retrying item stays in msdb forever, with its body, and contributes to the msdb growth that has its own check.

How to confirm it yourself

What is in the queue, and how it got there:

SELECT [sent_status],
       COUNT(*)                   AS [messages],
       MIN([send_request_date])   AS [oldest],
       MAX([send_request_date])   AS [newest]
  FROM msdb.dbo.sysmail_allitems WITH (NOLOCK)
 GROUP BY [sent_status]
 ORDER BY [messages] DESC;

The reason, which is the part that matters, from the Database Mail event log:

SELECT TOP (50)
       l.[log_date],
       l.[event_type],
       l.[description],
       i.[recipients],
       i.[subject]
  FROM msdb.dbo.sysmail_event_log AS l WITH (NOLOCK)
  LEFT JOIN msdb.dbo.sysmail_allitems AS i WITH (NOLOCK)
         ON i.[mailitem_id] = l.[mailitem_id]
 WHERE l.[event_type] IN ('error', 'warning')
 ORDER BY l.[log_date] DESC;

The description column carries the actual SMTP error, which is nearly always enough to diagnose it. And confirm the service is even running:

EXEC msdb.dbo.sysmail_help_status_sp;          -- STARTED or STOPPED
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_account_sp;

How to fix it

Read the error in sysmail_event_log first. The common ones map cleanly:

If the queue has stopped, start it:

EXEC msdb.dbo.sysmail_start_sp;

Test the path end to end once you think it is fixed:

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

Then check it actually left, rather than assuming:

SELECT TOP (5) [sent_status], [send_request_date], [sent_date], [subject]
  FROM msdb.dbo.sysmail_allitems WITH (NOLOCK)
 ORDER BY [mailitem_id] DESC;

Then clear the backlog. The Quick Scan offers this directly: right click the finding and choose one of the built in actions. Behind them:

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_status = 'failed';
EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_status = 'retrying';

Read what was in them before deleting, at least the subjects. A queue of failed messages is a list of things the instance tried to tell you, and some of them may still need acting on.

How long it takes

About two hours, most of it establishing what changed at the mail server, which usually means a conversation with whoever runs it.

Related reports

Related checks

Frequently asked questions

A few failures after a mail server migration are expected. Yes, and the thing to confirm is that current messages succeed. The sent_status breakdown above answers that in one query.

Can I just delete the failed items? Once you have read the reason and fixed it. Deleting first removes the evidence and the queue simply refills.

Messages say retrying and never change. Database Mail retries per its account configuration and then gives up. Persistent retrying usually means the mail server is accepting the connection and not the message.

Why one day? It keeps the finding about the current state. Issue 104 covers the same condition further back, and the queries above look at the whole history.