How to Troubleshoot SQL Server Database Mail Issues Using Built-In View
Sending emails from a SQL Server via sp_send_dbmail is common, but troubleshooting problems can be frustrating. Fortunately, SQL Server logs every email attempt, making it easier to find and fix issues.
View All Messages Processed By Database Mail
The sysmail_allitems view shows every email that Database Mail has tried to process, whether it was successful or not. This view can help you identify problems by showing you the details of the messages that were sent compared with the messages that weren’t sent by giving you the status of each email.
Here are the status values you’ll see:
| sent | The message was successfully sent. |
| unsent | The message is waiting to be sent. |
| retrying | Send attempt failed; retrying soon. |
| failed | The message could not be sent. |
View Sent Messages
Use sysmail_sentitems to see which messages were successfully sent. Database Mail marks a message as sent when it is successfully submitted and accepted by the SMTP mail server. However, just because an email was accepted by the mail server does not mean it was actually delivered to the recipient. This just means that SQL Server succeeded in giving the message to the email server, and it is up to the email server to actually send the email. If something went wrong after SQL Server passed it off (like a bounce or spam filter snag), you won’t see that here. Those kinds of issues are up to the mail server to handle and won’t show up in SQL Server logs.
View Unsent Messages
Use the sysmail_unsentitems view to find emails still waiting to be sent or retrying after a failure.
Messages marked unsent or retrying remain in the mail queue and could be sent at any time.
Normally, you will not see many messages here unless there is a delay or email processing issue.
Database Mail marks a message as unsent when it waits in the queue but has not yet been processed.
If a message shows a retrying status, Database Mail tried to send it but could not reach the SMTP server. Retry behavior depends on the settings for Account Retry Delay and Account Retry Attempts.
(For configuration details, see the sysmail_configure_sp stored procedure.)
View Failed Messages
The sysmail_faileditems view is used to return only the messages with the failed status. Use this view to determine which messages were not successfully sent by Database Mail and get message details to help you identify the nature of the problem.
To search for errors that are related to failed emails, use the sysmail_faileditems view to get the mailitem_id of the failed email, and then search for that mailitem_id in sysmail_event_log.
Microsoft also offers this extensive list of Database Mail troubleshooting techniques.
View Email Attachments
Use the sysmail_mailattachments view to list each attachment sent by Database Mail and its properties, such as file name and file size. This view does not store the attachment content, but it lets you track and verify attachments associated with each email. To link attachments to specific emails, cross-reference the mailitem_id with the other Database Mail views listed above.
Take Control of Your Database Mail
Don’t let email issues slow you down. Proactive SQL Server Database Mail troubleshooting keeps your environment stable and your team confident.
Tired of Troubleshooting SQL Server Issues Alone?
Let The SERO Group help you keep your SQL Servers running smoothly. Schedule a quick, no-pressure discovery call today.