Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Managed Instance
This topic describes how to check the status of the e-mail message sent using Database Mail in SQL Server by using Transact-SQL.
Before you begin:
To view the status of the e-mail sent using Database Mail, using: Transact-SQL
Database Mail keeps copies of outgoing e-mail messages and displays them in the sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems views of the msdb database. The Database Mail external program logs activity and displays the log through the Windows Application Event Log and the sysmail_event_log view in the msdb database. To check the status of an e-mail message, run a query against this view. E-mail messages have one of four possible statuses: sent, unsent, retrying, and failed.
To view the status of the e-mail sent using Database Mail
Select from the sysmail_allitems table, specifying the messages of interest by mailitem_id or sent_status.
To check the status returned from the external program for the e-mail messages, join sysmail_allitems to sysmail_event_log view on the mailitem_id column, as shown in the following section.
By default, the external program does not log information about messages that were successfully sent. To log all messages, set the logging level to verbose using the Configure System Parameters page of the Database Mail Configuration Wizard.
The following example lists information about any e-mail messages sent to danw
that the external program could not send successfully. The statement lists the subject, the date and time that the external program failed to send the message, and the error message from the Database Mail log.
USE msdb ;
GO
-- Show the subject, the time that the mail item row was last
-- modified, and the log information.
-- Join sysmail_faileditems to sysmail_event_log
-- on the mailitem_id column.
-- In the WHERE clause list items where danw was in the recipients,
-- copy_recipients, or blind_copy_recipients.
-- These are the items that would have been sent
-- to danw.
SELECT items.subject,
items.last_mod_date
,l.description FROM dbo.sysmail_faileditems as items
INNER JOIN dbo.sysmail_event_log AS l
ON items.mailitem_id = l.mailitem_id
WHERE items.recipients LIKE '%danw%'
OR items.copy_recipients LIKE '%danw%'
OR items.blind_copy_recipients LIKE '%danw%'
GO
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Set up email in Dynamics 365 Business Central - Training
Learn how to set up accounts, compose and send email messages, attach documents, assign scenarios, send sales documents, and check the outbox and sent emails in Microsoft Dynamics 365 Business Central.
Documentation
sysmail_allitems (Transact-SQL) - SQL Server
sysmail_allitems contains one row for each message processed by Database Mail. Use this view when you want to see the status of all messages.
sysmail_event_log (Transact-SQL) - SQL Server
sysmail_event_log (Transact-SQL)
sysmail_sentitems (Transact-SQL) - SQL Server
sysmail_sentitems (Transact-SQL)