In this article I will explain with an example, how to check email queue in
SQL Server.
Checking Email Queue using Log Tables
In SQL Server, the email is queued in the msdb.dbo.sysmail_unsentitems table until they are sent.
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 Email Queue 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
