Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Purging MSDB Backup and Restore History from SQL Server


By:   |   Read Comments (2)   |   Related Tips: More > Backup

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

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?

Solution

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
go
exec sp_delete_backuphistory '2009-04-02' 
go

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:

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
go
exec sp_delete_database_backuphistory 'test1' 
go

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 2005/2008 Books Online
  • Read SQL Server MVP Geoff Hiten's blog entry about adding indexes to the msdb database tables to efficiently support purges


Last Update:


signup button

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





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

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,
 CONSTRAINT [PK_DataPurgeHistory] PRIMARY KEY CLUSTERED 
(
	[DBName] ASC,
	[TableName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
       ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

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

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

Friday, April 10, 2009 - 11:35:47 AM - brian@escription.com Back To Top

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.


Learn more about SQL Server tools