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:
- dbo.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
Strate SQL 