What Mail Items Have Been Sent With Database Mail

Thursday Aug 3rd 2017 by Greg Larsen
Share:

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, Greg Larsen shows you how to review the database mail items that have been processed by SQL Server.

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   DJ\SQLAgent                 Nightly Backup completed successfully
2017-06-19 23:30:00.517   DJ\SQLAgent                 DW load completed successfully
2017-06-20 21:07:58.433   DJ\SQLAgent                 Nightly Backup completed successfully
2017-06-20 23:30:01.150   DJ\SQLAgent                 DW load completed successfully
2017-06-21 21:08:02.460   DJ\SQLAgent                 Nightly Backup completed successfully
2017-06-21 23:30:00.653   DJ\SQLAgent                 DW load completed successfully
2017-06-22 21:05:14.347   DJ\SQLAgent                 Nightly Backup completed successfully
2017-06-22 23:30:00.907   DJ\SQLAgent                 DW load completed successfully
2017-06-23 21:15:52.870   DJ\SQLAgent                 Nightly Backup completed successfully
2017-06-23 23:30:01.143   DJ\SQLAgent                 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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved