What Mail Items Have Been Sent With Database Mail

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.   

See all articles by Greg Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles