Disk space these days is not as expensive as it used to be, but as a DBA I like to keep things tidy and optimize as much things as possible. Cleaning up databases and old backups, archiving databases are some of my regular tasks.

There will be a couple examples on MSDB database cleanup in this post with screenshots and reports.

MSDB database contains a lot of historical records related to the email, backups and maintenance plans. It does not usually grow too fast, but it may have a lot of historical information in large environments. If you do not maintain the history properly msdb sometimes can have years of history.

Keep in mind that some of the historical information is required for databases restores, for audit, etc. So, before you start cleaning up your database make sure that this does not go against your policies. Some DBAs base their restore scripts on the msdb database’s tables, this also has to be taken into consideration.

Some settings for the job history could be found under SQL Server Agent. Ideally you would setup “History Cleanup Task” using Maintenance Plans in SQL Server Management Studio (SSMS). This is quick and easy way:

But there are circumstances when you may need to do this manually, for example: SQL Server Agent is disabled on SQL Server; you inherited old SQL Server where history has not been cleaned up since server setup. Also, keep in mind that “History Cleanup Task” does not include Database Mail logs.

Manual Cleanup

Audit requirements in this example will be to keep 6 months of historical data and 6 months of backup history (based on Service Level Agreements). This is not real life scenario that we will be only using it to demonstrate database size changes after the cleanup.

I would like to keep email sending log for the last 6 months as well as evidence for auditors that I get e-mails about critical system errors.

Here is the msdb database size on SQL Server with 50 databases and 1 year of history:

Let’s run SSMS report showing Disk Usage by Top Tables:

Step 1: Backup-restore history cleanup

This is done by executing sp_delete_backuphistory stored procedure. Here are the tables affected (as per BOL):

“… sp_delete_backuphistory must be run from the msdb database and affects the following tables:

• backupfile
• backupfilegroup
• backupmediafamily
• backupmediaset
• backupset
• restorefile
• restorefilegroup
• restorehistory

The physical backup files are preserved, even if all the history is deleted.”

The stored procedure’s parameters:

sp_delete_backuphistory [ @oldest_date = ] ‘oldest_date’

The script to cleanup backup-restore history for our example scenario:

EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = ‘January 1, 2012’

Step 2: Mail history cleanup

This is done by executing sysmail_delete_log_sp and sysmail_delete_mailitems_sp stored procedures.

The sysmail_delete_log_sp stored procedure deletes entries from the Database Mail log (msdb.dbo.sysmail_log table).

The stored procedure’s parameters:

sysmail_delete_log_sp [ [ @logged_before = ] ‘logged_before’ ]

[, [ @event_type = ] ‘event_type’ ]

The script to cleanup old mail log records for our example:

EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = ‘January 1, 2012’

The sysmail_delete_mailitems_sp stored procedure deletes e-mail messages from the Database Mail internal tables (msdb.dbo.sysmail_allitems table).

The stored procedure’s parameters:

sysmail_delete_mailitems_sp [ [ @sent_before = ] ‘sent_before’ ]

[ , [ @sent_status = ] ‘sent_status’ ]

The script to cleanup old mail items for our example scenario:

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = ‘January 1, 2012’ , @sent_status = NULL — all statuses

Step 3: Maintenance plans log cleanup

This is done by executing sp_maintplan_delete_log stored procedure.

The sp_maintplan_delete_log stored procedure permanently deletes entries and details from the Maintenance Plans log (msdb.dbo.sysmaintplan_logdetail and msdb.dbo.sysmaintplan_log tables).

The stored procedure’s parameters:

sp_maintplan_delete_log [ [@plan_id = ] ‘plan_id’ ]

[, [@subplan_id = ] ‘subplan_id’ ]

[, [@oldest_time = ] ‘oldest_time’ ]

The script to cleanup old maintenance plans log records for our example scenario:

EXEC msdb.dbo.sp_maintplan_delete_log @oldest_time = ‘January 1, 2012’

Step 4: Job history cleanup

This is done by executing sp_purge_jobhistory stored procedure.

The sp_purge_jobhistory stored procedure permanently deletes entries and details from the job history (msdb.dbo.sysjobhistory table).

The stored procedure’s parameters:

sp_maintplan_delete_log [ [@plan_id = ] ‘plan_id’ ]

[, [@subplan_id = ] ‘subplan_id’ ]

[, [@oldest_time = ] ‘oldest_time’ ]

The script to cleanup old maintenance plans log records for our example scenario:

EXEC msdb.dbo.sp_maintplan_delete_log @oldest_time = ‘January 1, 2012’

All steps in one script

DECLARE @date DATE

SET @date = ‘January 1, 2012’

EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @date

EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @date

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @date , @sent_status = NULL

EXEC msdb.dbo.sp_maintplan_delete_log @oldest_time = @date

EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @date

Now if we run the same database reports from SSMS as before we will see the difference:

The database’s size reduced by almost 64%!