Recently, I was asked why e-mails would mysteriously not be sent from one of the servers that I help maintain. One of the greatest things about working with SQL Server is most everything that happens in the server is data and all I had to figure out is where the data was stored.
In msdb there are a number of system views that present the data accumulated from the activity in Database Mail:
.sysmail_allitems
dbo.sysmail_event_log
dbo.sysmail_faileditems
dbo.sysmail_mailattachments
dbo.sysmail_sentitems
dbo.sysmail_unsentitems
Microsoft has an article on
troubleshooting Database Mail, but none of those seem to address the problem that I had at hand. To solve the mystery that was brought to me at the beginning of this post, I created the view below and in a not so miraculous way the DBA was able to see the failed e-mails in the system and reason for those failures.
SELECT
sfi.mailitem_id
,sfi.recipients
,sfi.subject
,sfi.send_request_date
,sfi.sent_status
,sfi.sent_date
,sel.description
FROM msdb.dbo.sysmail_faileditems sfi
INNER JOIN msdb.dbo.sysmail_event_log sel ON sfi.mailitem_id = sel.mailitem_id
ORDER BY sfi.mailitem_id, sfi.sent_date
And for even more ease of use, I've attached a RDL with this information for those that want this to be pretty.