Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted August 3, 2017

What Mail Items Have Been Sent With Database Mail

By Greg Larsen

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM