Check the Status of EMail 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.

/*
Author: Our Tech Ideas
Created: 13/07/2022
Modified: 24/05/2023
Website: https://ourtechideas.com/
*/
USE msdb; -- Use the 'msdb' database

SELECT
  ai.profile_id,                           -- Profile ID
  p.name AS profile_name,                  -- Profile name
  ai.recipients,                           -- Recipients
  ai.subject,                              -- Subject
  CONVERT(varchar(12), ai.send_request_date, 103) AS [Sent Date],  -- Sent Date (formatted as dd-mm-yyyy)
  FORMAT(ai.send_request_date, 'hh:mm tt') AS [Sent Time],        -- Sent Time formatted in AM/PM format
  ai.sent_status,                          -- Sent Status
  ai.send_request_user                      -- Send Request User
FROM sysmail_allitems ai
JOIN sysmail_profile p ON ai.profile_id = p.profile_id
WHERE CAST(ai.send_request_date AS date) = '2023-05-24'  -- Filter by Send Request Date
  -- AND ai.sent_status = 'failed'                       -- Additional filter: Sent Status is 'failed'
  -- AND ai.subject LIKE '%I love you%'                   -- Additional filter: Subject contains 'I love you'
  -- AND ai.recipients LIKE '%sanjay%humania%'            -- Additional filter: Recipients contain 'sanjay' or 'humania'
ORDER BY CONVERT(time(0), ai.send_request_date) DESC;    -- Order by Sent Time in descending order

Here’s a brief explanation of the provided T-SQL query

1. The query is written to retrieve information from the “msdb” database, which is used for managing SQL Server-related tasks.
2. It selects various columns from the “sysmail_allitems” and “sysmail_profile” tables.
3. The “sysmail_allitems” table stores information about sent emails, and the “sysmail_profile” table contains details about email profiles.
4. The query retrieves the profile ID, profile name, recipients, subject, sent date, sent time (in AM/PM format), sent status, and the user who initiated the email sending.
5. The query filters the results to only include emails sent on a specific date (in this case, May 24, 2023).
6. There are optional additional filters that can be uncommented and customized to further narrow down the results based on the sent status, subject, or recipients of the emails.
7. The results are sorted in descending order based on the sent time.