In this article I will explain with an example, how to check if
sp_send_dbmail was successful in
SQL Server.
The
Stored Procedure sp_send_dbmail logs the email
msdb.dbo.sysmail_sentitems table which notifies that email has been successfully sent.
Checking sp_send_dbmail was Successful in SQL Server
Once the emails are successfully sent, the
Stored Procedure sp_send_dbmail logs the email
msdb.dbo.sysmail_sentitems table.
The following SQL query will be used to check the Queued emails in SQL Server.
SELECT * FROM msdb.dbo.sysmail_unsentitems
In case, you did not find the email in the above table, then there are two cases.
Case 1: Email has been sent
The Emails which were sent without any problems are available in the msdb.dbo.sysmail_sentitems table.
SELECT * FROM msdb.dbo.sysmail_sentitems
Case 2: Email has failed
The Emails which are failed and were not sent are available in the msdb.dbo.sysmail_faileditems table.
SELECT * FROM msdb.dbo.sysmail_faileditems
![Check if sp_send_dbmail was Successful in SQL Server]()
Above table does not store the details of the Error i.e. the Error Message. The details of the Error are present in the msdb.dbo.sysmail_event_log table.
The following SQL Query gets list of failed emails as well as the details of the Error.
SELECT mailitem_id
,[subject]
,[last_mod_date]
,(SELECT TOP 1 [description]
FROM msdb.dbo.sysmail_event_log
WHERE mailitem_id = logs.mailitem_id
ORDER BY log_date DESC) [description]
FROM msdb.dbo.sysmail_faileditems logs
Downloads
