Database Mail Health Check: Unsent Items and the Event Log

Database Mail health is part of every alert you rely on, because an alert nobody receives is the same as no alert. Check that the feature is on, see what is waiting in the queue, and read the event log for the reason.

The alert ran, but nobody got the email

A disk space alert you built last year did not reach you this week. The job ran. Agent says it succeeded. The email simply never arrived.

Here is the catch. sp_send_dbmail does not send anything itself. It drops a request into a queue in msdb, and a separate process picks it up later. The job can succeed while delivery fails an hour afterward, and nobody hears about it. All the queries below only read. They send nothing and change nothing. You need rights in msdb to run them.

Step one: is Database Mail turned on?

Start with the feature switch. A value_in_use of 1 means Database Mail is on, and 0 means it is off. Then ask the status procedure. I wrap it in TRY and CATCH so an error shows up as a number instead of a wall of red.

SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'Database Mail XPs';

BEGIN TRY
    EXEC msdb.dbo.sysmail_help_status_sp;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;

My test instance has the feature off. The first query returns 0, and the status procedure fails with error 15281, which means SQL Server blocked it because Database Mail is turned off. That is a finding by itself. On a server where mail works, the status procedure should answer instead of failing.

Step two: what is waiting in the queue?

Every request lands in msdb with a status: sent, unsent, retrying, or failed. Count them.

SELECT sent_status, COUNT(*) AS ItemCount
FROM msdb.dbo.sysmail_allitems
GROUP BY sent_status
ORDER BY sent_status;

My test instance has no mail history, so this returns no rows. Your production server will not be so quiet. A big count of unsent or retrying is a backlog. A big count of failed means something is rejecting the mail.

Read the oldest waiting item

A count alone does not tell you whether a backlog is new or old. Age does. Since my test instance has no mail history, the next block builds five made-up rows with the same column names as the real view. The query is the one you would run against msdb. I use a fixed clock of 10:00 so the numbers stay the same each time.

DROP TABLE IF EXISTS #MailItems;

CREATE TABLE #MailItems
(mailitem_id int, sent_status varchar(8), send_request_date datetime, last_mod_date datetime);

INSERT #MailItems VALUES
    (1, 'sent',     '2025-01-01T08:00:00', '2025-01-01T08:00:05'),
    (2, 'sent',     '2025-01-01T08:10:00', '2025-01-01T08:10:04'),
    (3, 'failed',   '2025-01-01T08:30:00', '2025-01-01T08:31:00'),
    (4, 'unsent',   '2025-01-01T09:00:00', '2025-01-01T09:00:00'),
    (5, 'retrying', '2025-01-01T09:05:00', '2025-01-01T09:20:00');

DECLARE @Now datetime = '2025-01-01T10:00:00';

SELECT mailitem_id, sent_status, send_request_date,
       DATEDIFF(minute, send_request_date, @Now) AS WaitingMinutes
FROM #MailItems
WHERE sent_status IN ('unsent', 'retrying', 'failed')
ORDER BY send_request_date, mailitem_id;

Item 3 failed and has been sitting for 90 minutes. Item 4 is unsent after 60 minutes, which is suspicious for a system that normally takes seconds. Item 5 is still retrying after 55. What counts as too old depends on your environment, so pick a number and write it down.

Here is the real query against msdb. It returns nothing on my test instance.

SELECT mailitem_id, sent_status, send_request_date, last_mod_date,
       DATEDIFF(minute, send_request_date, SYSDATETIME()) AS WaitingMinutes
FROM msdb.dbo.sysmail_allitems
WHERE sent_status IN ('unsent', 'retrying', 'failed')
ORDER BY send_request_date, mailitem_id;

I left out the subject, body, and recipients on purpose. Keep them out of broad reports unless someone needs them.

Step three: ask the event log why

The status tells you that something is stuck. The event log tells you why. Filter by mailitem_id for one message, or look at the last day for everything.

SELECT TOP (100) log_id, log_date, event_type, mailitem_id, account_id, description
FROM msdb.dbo.sysmail_event_log
WHERE log_date >= DATEADD(day, -1, SYSDATETIME())
ORDER BY log_date DESC, log_id DESC;

Login problems, network problems, and a rejecting mail server all leave different messages, and they need different fixes. Paste only the error category into a shared ticket. Descriptions can contain server and account names.

Do not let Database Mail watch itself

If the alert that says mail is broken depends on mail, you will never read it. Watch the queue from a monitoring tool that uses another route. Also send a real test message on a schedule, and confirm it arrives in an actual inbox. A sent status only means the mail server accepted it.

DROP TABLE IF EXISTS #MailItems;

If you want a wider look at the health of your whole server, I wrote about it in 50 Minutes vs. 4 Hours for Database Performance Health Check.

The next time an alert goes quiet, check the mail before you doubt the alert.

A sent status is not inbox delivery, it is transport acceptance.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.