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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted August 3, 2017

Purging Old Database Mail Items

By Greg Larsen

Are you using database mail to send SQL Agent job completion messages, and alerts?  If so, are you also purging the old mail items stored in msdb?  In this tip, I will show you how to purge database mail.

Database mail is stored in the msdb database.  If you don’t purge it periodically your mail items will take up more and more space in the msdb database.   How big is your msdb database, right now?   If you are sending lots for database mail without purging your database mail periodically then very possibly your database mail might be taking up lots space.  

There is a system stored procedure that you can use to delete the mail items stored in the msdb database.  It is called sysmail_delete_mailitems_sp.  This stored procedure can be used to delete your mail items by date, and/or mail sent status. There are four different mail send status values: sent, unsent, retrying and failed.  Below you will find a couple of examples on how to use this stored procedure.

Suppose at your shop you have decided that you want to retain the last 30 days of database mail.  To deleted all the database mail that is older than 30 days you can run the following code:    

DECLARE @PurgeDate datetime = dateadd(dd,-30,getdate());
EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before=@PurgeDate;

You may choose to only delete mail items that have been successfully sent.  If that is the case then you can run the following code to delete your database mail based on the sent­_status of “sent”:

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_status='sent';

If you are not periodically deleting your mail then you might want to consider cleaning up old database mail that no longer has value.  Determine what retention period you should use for your database mail.  With your established retention period then just set up a SQL Agent job to purge your database mail based on your retention period.

See all articles by Greg Larsen

MS SQL Archives

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