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
- The query is written to retrieve information from the “msdb” database, which is used for managing SQL Server-related tasks.
- It selects various columns from the “sysmail_allitems” and “sysmail_profile” tables.
- The “sysmail_allitems” table stores information about sent emails, and the “sysmail_profile” table contains details about email profiles.
- 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.
- The query filters the results to only include emails sent on a specific date (in this case, May 24, 2023).
- 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.
- The results are sorted in descending order based on the sent time.