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 SP:

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.