How Much Backup History is Too Much?

How much is too much when it comes to backup history. What we are talking about here is how much backup history do you want to keep, backup sizes, what file was written, etc. It is nice to keep as much as possible so that you can learn from this over time, but how much is too much depends on a number of things:

• How often are backups running? Do you have a single weekly full backup for one database, or do you have log backups every 5 minutes for 100+ databases.
• How long do you keep your backup files for? If you only keep your backup files for 2 weeks, but you have 8 years of backup history logged in tables, is that a bit excessive?
• How big do you want your MSDB database to be? Years of data stored in the backup history tables in MSDB can really bloat out the database.
• How fast is it to access insert into these tables. If they are too large it can really slow things down.

After thinking over these questions, you need to decide. I usually pick a number somewhere between 30 and 90 days to keep the backup history. For databases with frequent transaction log backups and/or a large number of databases I usually go with a smaller number.

Here is a script that you can use to purge your backup history over time. You will want to run this manually before putting it on a job to confirm that it runs quickly. If you have too much history you may need to purge some at a time. For instance start by purging older than 5 years, then older than 4 years, and so on, depending on performance.

EXEC msdb.dbo.sp_delete_backuphistory @oldest_date =  '1/1/2015'; -- replace with a date of your choice

Or you can do something more elaborate to purge only a certain number of days since the oldest date:

DECLARE @startDate AS DATETIME;
SET @startDate = NULL;

SELECT @startDate = min(backup_start_date) 
  FROM msdb.dbo.backupset WITH (NOLOCK);

SET @startDate = dateadd(d, 5, @startDate);
IF  @startDate < getdate() - 20
BEGIN
	EXEC msdb.dbo.sp_delete_backuphistory @startDate;
END

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!