My company has a practice – Whatever email send out from Application must go through SQL Server MSDB.  Here is the sample query to retrieve mail items from MSDB database:

select top 10 sent_status, send_request_date,* from msdb..sysmail_mailitems with(nolock)
where send_request_date between ‘2012-11-27 00:00:02.000’ and ‘2012-11-28 00:00:02.000’
order by sysmail_mailitems.send_request_date desc

sent_status:
0 – unsent
1 – sent
2 – failed (default)
3 – retrying

If mail items is sent failed (sent status = 2), we can get the fail description by using this query. Adjust the mailitem_id accordingly:
SELECT *  FROM msdb..sysmail_log with(nolock)  where mailitem_id = ‘33235’

b2b-email-database

Advertisements