Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Database Mail Cleanup Procedures


By:   |   Read Comments (5)   |   Related Tips: More > Database Mail

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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.



Last Update:


signup button

next tip button



About the author
MSSQLTips author Ken Simmons Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, April 27, 2017 - 2:16:31 PM - me Back To Top

 

Thanks for you post, very helpful information 


Tuesday, March 11, 2014 - 11:12:48 PM - Prashanth Back To Top

Thanks for your post , i need a mail notification on msdb if any mail get failed in msdb


Monday, April 20, 2009 - 9:02:47 AM - sm8680 Back To Top

Is there anything for alerts like this as well?

 

Steve


Friday, April 17, 2009 - 8:58:42 AM - grobido Back To Top

Thanks Jerry.

I will update the tip to reflect that.


Friday, April 17, 2009 - 8:43:27 AM - jerryhung Back To Top

Thanks for the tip, exceptI think this should be changed from inline assignment (only in 2008) to 2 line declariation+assignment

 from

 DECLARE @DeleteBeforeDate DateTime = (Select DATEADD(d,-30, GETDATE()))

on SQL 2005, erros out with Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '('.

 to

DECLARE @DeleteBeforeDate DateTime
SET @DeleteBeforeDate = DATEADD(d,-60, GETDATE())


Learn more about SQL Server tools