Purging Old Database Mail Items

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

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