SQL Server Database Mail Cleanup Procedures

SQL Server Database Mail Cleanup Procedures

Written By: Ken Simmons -- 4/17/2009 -- 2 comments

Stay informed - get the MSSQLTips.com newsletter and win - click here

Problem
I have been using Database Mail for a while and it is causing a lot of growth in my msdb database. What is the best way to delete the excess data stored in the msdb due to Database Mail?

Solution
There are two system stored procedures you can take advantage of in order to clean up Database Mail messages, attachments and log entries stored in the msdb database.   These two system stored procedures are sysmail_delete_mailitems_sp and sysmail_delete_log_sp.

You can execute the sysmail_delete_mailitems_sp stored procedure located in the msdb database to:

• delete all mail messages
• delete messages older than a given date
• delete messages with a given status or
• delete messages older than a given date with a certain status.

The complete syntax is shown below. If you execute the sysmail_delete_mailitems_sp stored procedure without any parameters, all mail messages will be deleted.

sysmail_delete_mailitems_sp [ [ @sent_before = ] 'sent_before' ] -- '1/1/2009' [ , [ @sent_status = ] 'sent_status' ] -- sent, unsent, retrying, failed

You can execute the sysmail_delete_log_sp stored procedure located in the msdb database to:

• delete all log entries
• delete all log entries prior to a given date
• delete log entries for a certain event type or
• delete delete log entries prior to a given date for a certain event type.

The complete syntax is shown below. If you execute the sysmail_delete_log_sp stored procedure without any parameters, all log entries will be deleted.

sysmail_delete_log_sp [ [ @logged_before = ] 'logged_before' ] --'1/1/2009' [, [ @event_type = ] 'event_type' ] --success, warning, error, informational

You should come up with a retention policy and schedule a job to run periodically to clean up the Database Mail history. For example, the following script will delete all mail entries older than thirty days.

DECLARE @DeleteBeforeDate DateTime = (Select DATEADD(d,-30, GETDATE())) EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate EXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate

Note: the syntax above in the first line is new for SQL 2008 where you can declare and set a value at the same time.  For SQL 2005 you would need to do this with two lines, first the DECLARE and then setting the value as follows:

DECLARE @DeleteBeforeDate DateTime SELECT @DeleteBeforeDate = DATEADD(d,-30, GETDATE()) EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate EXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate

Next Steps
There is a lot of information logged in the msdb that requires your attention in order to prevent excessive growth such as backup and restore history and Maintenance Plan history. You should review the following tips to help you maintain a clean, manageable msdb database.