Check If sp_send_dbmail Was Successful

Question

I am looking for a way to check if a particular e-mails queued using sp_send_dbmail are eventually successful sent from our Exchange server. I've looked at the system tables msdb.dbo.sysmail_mailitems and msdb.dbo.sysmail_log. msdb.dbo.sysmail_log seems to be the more helpful of the two; specifically, its description column. From the tests I've conducted so far, it seems whenever an error occurs, a message in the following format appears in the description column:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2012-11-01T11:28:04). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for [email protected]). )

This is enclosed with other rows that share the same process_id. The description for the enclosing records are

DatabaseMail process is started

and

DatabaseMail process is shutting down

If an e-mail was successfully sent, the same 2 rows are recorded in the table, except with no enclosed rows between them.

So if I have a successful send, the following appears in the table

and if I have a send failure, the log records this

Are there other instances of how the entries could be logged if a send failed or if a send was successful? For example, could it be possible that there are 4 rows of entries for a send (2 enclosing stating when it was started and when it shut down, and 2 enclosed stating the e-mail was successfully sent). I've found no log records that diverged from the pattern listed above, but would like to be sure before I write logic based on this assumption.

Answer 1

sysmail_faileditems will only get you the list of failed emails. If you need to see a list of successfull emails you need to use sysmail_mailitems.

Use the below query to get details for all emails sent the same date:

SELECT * FROM msdb..sysmail_mailitems WHERE sent_date > DATEADD(DAY, -1,GETDATE()) 

And here is the complete query to get all the failed emails from the past 24 hours:

SELECT items.subject ,        items.recipients ,        items.copy_recipients ,        items.blind_copy_recipients ,        items.last_mod_date ,        l.description FROM   msdb.dbo.sysmail_faileditems AS items        LEFT OUTER JOIN msdb.dbo.sysmail_event_log AS l                      ON items.mailitem_id = l.mailitem_id WHERE  items.last_mod_date > DATEADD(DAY, -1,GETDATE())