Purging MSDB Backup and Restore History Tables

Today while checking our database growth reports, I noticed that size of msdb database on most of our SQL Server instances is greater than 2GB.  After further investigation, I noticed that it was due to the size of the following backup/restore history tables:

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

This is because SQL Server stores the information about that backup/restore activity inside these backup/restore history tables. It is advisable, to regularly recycle these backup and restore history tables if the history is no longer required.  Luckily, Microsoft provides system stored procedures sp_delete_backuphistory which you can use to recycle these history tables. This procedure deletes the backup and restores history older than a provided date.

Here is the syntax for calling this stored procedure:

USE [msdb]
GO

--Delete all backup/restore history prior to a specified date
EXEC sp_delete_backuphistory [ @oldest_date = ] 'oldest_date';
GO

I've executed this procedure as follow on all over SQL Servers to delete backup history older than 3 months:

USE [msdb]
GO

DECLARE @OldestDate [smalldatetime]

SET @OldestDate = GETDATE() - 91

EXEC [msdb]..[sp_delete_backuphistory] @OldestDate;
GO

Note: If you've a lot of data to purge and you want to use sp_delete_backuphistory stored procedure, then look at purging in batches to minimize contention on the msdb tables. Also rebuild all indexes of backup and restore history tables before and after executing this procedure otherwise deletes will take long and backups and restore will take longer to complete due to fragmented indexes after delete.

About these ads

One thought on “Purging MSDB Backup and Restore History Tables

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s