Purging MSDB Backup and Restore History from SQL Server

By:   |   Updated: 2009-04-10   |   Comments (2)   |   Related: More > Backup


In one of your recent tips, you mention that SQL Server keeps a history of all backups and restores that have been made in the system. Our organization performs backups and restores frequently and I've noticed our msdb database is very large because of this. How can I purge some of this data and free some database space?


As mentioned in this tip, SQL Server keeps records of backup and restore activity in system tables within the msdb database. Without regular maintenance, these system tables can grow to very large sizes leading to a large overall size for the msdb database.

These msdb tables include:

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

Luckily, Microsoft provides two system stored procedures that you can use to trim the size of the msdb database tables that hold this data. The first procedure deletes all backup and restore history older than a provided date. The 2nd procedure allows you to delete all backup and restore history for a specific database. Let's look at these in more detail.

On my system, looking at msdb tables backupset and restorehistory, I see the following database backups and restores that have been performed:


The first procedure that can be used is sp_delete_backuphistory. This system stored procedure takes a single parameter - a cutoff date. Any data older than the supplied date is purged from the msdb tables listed earlier in this tip. In the following example, I'll purge all data older than 4/2/2009.

-- delete all backup/restore history prior to a specified date
use msdb
exec sp_delete_backuphistory '2009-04-02' 

Examining the tables again, we see that all backup history prior to 4/2/2009 has been purged with restorehistory tied to these entries being purged as well:

database name

The 2nd system stored procedure allows you to delete historical backup data for a specific database and is called sp_delete_database_backuphistory. Unfortunately, this procedure does not offer the finer option of choosing a cutoff date; It's all or nothing. In the next example, I'll delete all backup history for a specific database.

-- delete all backup history for a specific database 
use msdb
exec sp_delete_database_backuphistory 'test1' 

As you can see, only history for the specified database has been purged:

Next Steps
  • If you're concerned about the size of your msdb database, look at incorporating these system procedures as part of your routine database maintenance
  • If you have a lot of data to purge and you want to use sp_delete_backuphistory look at purging in batches to minimize contention on the msdb tables
  • Read this tip about analyzing and correcting a large SQL Server MSDB database
  • Read more about sp_delete_backuphistory and sp_delete_database_backuphistory in the SQL Server Books Online
  • Read SQL Server MVP Geoff Hiten's blog entry about adding indexes to the msdb database tables to efficiently support purges

Last Updated: 2009-04-10

get scripts

next tip button

About the author
MSSQLTips author Armando Prato Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.

View all my tips
Related Resources

Comments For This Article

Thursday, March 13, 2014 - 5:56:34 AM - demoan Back To Top (29737)
USE [DataPurge]




CREATE TABLE [dbo].[DataPurgeHistory](
	[DBName] [varchar](50) NOT NULL,
	[TableName] [varchar](50) NOT NULL,
	[DateStamp] [datetime] NULL,
	[PurgeNeeded] [bit] NOT NULL,
	[NewTable] [bit] NOT NULL,
	[YearsToKeep] [int] NULL,
	[PurgeField] [varchar](50) NULL,
	[DBName] ASC,
	[TableName] ASC



ALTER TABLE [dbo].[DataPurgeHistory] ADD CONSTRAINT [DF_DataPurgeHistory_PurgeNeeded]  DEFAULT ((0)) FOR [PurgeNeeded]

ALTER TABLE [dbo].[DataPurgeHistory] ADD CONSTRAINT [DF_DataPurgeHistory_NewTable]  DEFAULT ((1)) FOR [NewTable]

Friday, April 10, 2009 - 11:35:47 AM - [email protected] Back To Top (3166)

Yep my company takes a full backup of SQL Server nightly and transaction log backups every 15 minutes or basically 96 backups a day.

Furthermore we can only keep 1 or 2 days worth on the main server and we copy them off to an off-site location, so purging that database is critical.

Here are two lesser known pitfall about the purge backup history command:

1. There is a missing index in msdb so create it yourself

Create a non-clustered 
Index on msdb.dbo.backupset.media_set_id
This can shave hours off of deleting even a month's worth of backups.  Discovered it in SQL 2000 still use it in 2005

2. The stored procedure that deletes the backup history uses cursors in an inappropriate way, you could re-create the procedure to not use the cursors.  If you purge routinely (nightly) it isn't really necessary, but it does show that MSFT does not follow their own advice, and worse yet I have had a tech say NEVER use a cursor, and when I show him what I want to do he says use the SP for each database, guess what that uses a cursor.

Good luck.


Recommended Reading

Simple script to backup all SQL Server databases

Script to retrieve SQL Server database backup history and no backups

How to monitor backup and restore progress in SQL Server

Backup to multiple files for faster and smaller SQL Server files

Changing the default SQL Server backup folder

get free sql tips
agree to terms

Learn more about SQL Server tools