Is your msdb database growing bigger every day? If so you might want to consider purging some of the history records from the msdb database. One of those types of history records that are stored in the msdb database are records associated with using database mail. If you are sending email using database mail and you are not periodically purging email information, then you might find out this is one of the reasons your msdb database is growing bigger.
In order to remove historic mail from the msdb database, Microsoft has provided two system stored procedures. These stored procedures are named: sysmail_delete_mailitems_sp, and sysmail_delete_log_sp.
The “sysmail_delete_mailitems_sp” permanently deletes email messages from msdb internal tables that contain database mail items. This stored procedure allows you to delete email messages based on a date, or a specific status (sent, unsent, retry or failed). If you want to delete email messages using a date this stored procedure will delete all email messages that are less than or equal to the date past to this stored procedure. If no parameters are passed than all email messages will be deleted.
The “sysmail_delete_log_sp” stored procedure deletes database mail log entries. With this stored procedure you can delete log records based on a date and/or an event type. When you pass a date value to this stored procedure it will delete all database mail log entries that are older or equal to the date specified. If you want to delete mail log records based on one of the event types (success, warning, error or informational) then you use the event_type parameter. If no parameters are passed, then all mail log entries are deleted.
In reality you probably don’t need to keep email and log items forever. You will need to decide in your environment how long you need to keep database mail information in the msdb database. Once you know how long to keep mail information in msdb you can then build a process that purges your old database mail information using one or both of the system stored procedures listed above.
Below you will find a script that delete all database email items and log entries from msdb database that are older than or equal 90 days:
USE msdb; GO DECLARE @PurgeDate datetime; --Calculate PurgeDate 90 days from today SET @PurgeDate = dateadd(dd,-90,getdate()); -- Remove email and email attachments that are older or equal to @PurgeDate EXEC sysmail_delete_mailitems_sp @sent_before = @PurgeDate; -- Removes rows from sys.sysmail_log that are older or equal to @PurgeDate EXEC sysmail_delete_log_sp @logged_before = @PurgeDate;
