Database Mail Failed Items Log

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:

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.

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