Check the Status of E-Mail Messages Sent With Database Mail
Check the Status of E-Mail Messages Sent With Database Mail
In SQL Server sp_send_dbmail use to send emails and the SQL Server log all the status of each message processed by Database Mail. Below is the quick list of Database Mail Views you can query to find the status of each sp_send_dbmail call.
- To see messages with the failed status, use sysmail_faileditems.
- To see all messages processed by Database Mail, use sysmail_allitems.
- To see unsent messages, use sysmail_unsentitems.
- To see messages that were sent, use sysmail_sentitems.
- To view e-mail attachments, use sysmail_mailattachments.
- To view Windows or SQL Server messages returned by the Database Mail system, use sysmail_event_log.
Here is a simple TSQL script to find the status of the email sent from the SQL Server.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
USE msdb GO SELECT recipients ,subject ,convert(VARCHAR(12), send_request_date, 103) [Sent Date] ,CAST(send_request_date AS TIME(0)) [Sent Time] ,sent_status FROM sysmail_allitems WHERE cast(send_request_date AS DATE) = '13-june-2022' -- Sent Data --and subject like '%Mail Sent Through alertsprofile%' -- Search by subject name --And recipients like '%sanjay%humania%' -- Search by recipients name ORDER BY CAST(send_request_date AS TIME(0)) DESC |
Query output will be as below.
Facebook Comments Box