Check If sp_send_dbmail Was Successful

问题:

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 [e-mail]). )

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.

回答1:

This link from Microsoft seems to be useful - How to: Check the Status of E-Mail Messages Sent With Database Mail (Transact-SQL). See also related topics on Database Mail Logging and Auditing, Troubleshooting Database Mail.

回答2:

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())