Most shops use database mail for sending email alerts. Additionally, you might have some applications that are sending database mail. Database mail items are stored in the msdb database.
Have you ever wondered how much database mail you have sent in the past day or week? What about those database mail items that were not successfully sent. In this tip, I will show you how to review the database mail items that have been processed by SQL Server.
There are a number of views that can be used to read the database mail that has been sent. In this example, I use the sysmail_sentitem view to review the mail items that have been sent in the last week.
SELECT send_request_date , send_request_user , subject FROM msdb.dbo.sysmail_sentitems WHERE sent_date >= DATEADD(dd,-7,getdate());
Here is a snippet of output I get when I run this code on one of my SQL Server machines:
send_request_date send_request_user subject 2017-06-19 21:07:44.767 DJSQLAgent Nightly Backup completed successfully 2017-06-19 23:30:00.517 DJSQLAgent DW load completed successfully 2017-06-20 21:07:58.433 DJSQLAgent Nightly Backup completed successfully 2017-06-20 23:30:01.150 DJSQLAgent DW load completed successfully 2017-06-21 21:08:02.460 DJSQLAgent Nightly Backup completed successfully 2017-06-21 23:30:00.653 DJSQLAgent DW load completed successfully 2017-06-22 21:05:14.347 DJSQLAgent Nightly Backup completed successfully 2017-06-22 23:30:00.907 DJSQLAgent DW load completed successfully 2017-06-23 21:15:52.870 DJSQLAgent Nightly Backup completed successfully 2017-06-23 23:30:01.143 DJSQLAgent DW load completed successfully
With this view, you can see that there are two SQL Agent jobs that are sending emails daily.
The sysmail_sentitems view has other columns of database mail information you might find useful, like the actual body of the email, or who was the recipient of the database mail.
SQL Server has five additional views that can return information about the database mail stored in the msdb database:
sysmail_allitems – Returns one row for each mail item sent.
sysmail_event_log – Returns one row for each system or database mail error log message.
sysmail_faileditems – Returns one row for each database mail request that failed.
sysmail_mailattachements – Returns one row for each email attachment sent.
sysmail_unsentitems – Returns one row for each mail item that was not sent.
You can use these views to return different email messages that you would like to review. Next time you have a question about what database mail SQL Server just use one of these views.
