How To: Clear Backup History

One thing we regularly come across with a new client is large system databases. Something that SQL Server should do but doesn’t is clearing up system tables. One of the more common issues is a very large backup history which causes bloat in the msdb database. Let’s find out how to clear backup history.

These tables store information about backups that you have taken, which is extremely useful … for a time. After that useful time, it’s just unnecessary data that is taking up space on your server. There’s a number of related tables that store backup history, and it’s not simple to delete data from them all. Most don’t have dates stored in the table so you have to join them together and make sure you delete in the correct order.

Helpfully, Microsoft has provided a stored procedure to do this for us.

sp_delete_backuphistory

This is a system stored proc in msdb. It takes a date parameter and deletes anything older than that in the underlying tables.

Caution is needed with this stored procedure. Deleting a large amount of data can cause issues. It takes time and locks the tables, which will stop any of your backups from inserting data and may cause failures. It can also become a very large transaction, which is likely to cause issues in your log files.

First of all, let’s see if we’ve got a problem here. This query will look at the tables involved in backups and check their total size and any free space there might already be in the table:

Source code

USE[msdb]
GO
SELECT 
     t.name TableName
    ,s.name SchemaName
    ,p.ROWS TotalRows
    ,CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalMB
    ,CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.partitions p ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name IN 
('backupset','backupfile','backupfilegroup','backupmediafamily','backupmediaset','restorefile','restorefilegroup','restorehistory')
GROUP BY t.name, s.name, p.ROWS
ORDER BY TotalMB DESC, t.name

Now, what constitutes as ‘big’ will vary from environment to environment, so that decision is left up to the reader. But if your number looks big to you then we can check how much history we’re storing here:

Source code

SELECT
 
     DATEPART(YEAR,[backup_start_date]) BackupYear
 
    ,DATEPART(MONTH,[backup_start_date]) BackupMonth
 
    ,COUNT(*) BackupCount
 
FROM [msdb].[dbo].[backupset]
 
GROUP BY DATEPART(YEAR,[backup_start_date]), DATEPART(MONTH,[backup_start_date])
 
ORDER BY BackupYear, BackupMonth

The result of this query will be a list of data, by year and month, we have stored and the number of related records. If you have no process to clean these up currently then these will be back to the original date the server was created.

How long you need to keep this data is a business decision, so you will need to make this determination yourself (as a company), but most businesses don’t need any more than three months of backup history for their SQL Server needs. Now, it’s worth mentioning that there may be compliance reasons why you would need to keep more than this so it’s always worth double checking if you’re unsure.

Let’s assume we only want to keep backup history for the last three months. We want to do this in batches rather than one big chunk. It’s much easier on your server and will minimize impact on other processes.

There are two parameters in the code below that you will need to change to suit your needs:

• @DeleteRange. This is the size of the chunks of data that you want to delete; by default we’ll delete a month’s worth of data at a time, but you can set it to any parameter accepted by the DATEDIFF function.
• @Retain. This is how many of the previously chosen ranges you wish to keep. If @DeleteRange = ‘MONTH’ then @retain = 3 will keep three months of data. If you change @DeleteRange to ‘DAY’ and don’t change @retain, then you’ll only be keeping three days worth of data.

We’ll have a log table at the end of this at msdb.dbo.BackupDeleteLog. This is so you can see what has been happening and how long each batch took to delete. You may look at this and decide that your batches are too large or too small and that’s ok. You can just stop the code, change the parameters above, and run it again.

Source code

USE [msdb]
GO
/* user variables */
DECLARE  @DeleteRange NVARCHAR(100) = 'MONTH'	/* HOUR,DAY,WEEK,MONTH,YEAR */
		,@Retain INT = 3						/* same units as the @DeleteRange variable */
 
/* stuff we'll use later */
DECLARE  @DATE DATE
		,@loop INT
		,@SQL NVARCHAR(MAX)
		,@BatchStart DATETIME = GETDATE()
		,@DeleteDate DATETIME
 
/* create log table */
IF OBJECT_ID(N'msdb.dbo.BackupDeleteLog') IS NULL
BEGIN
	CREATE TABLE msdb.dbo.BackupDeleteLog
	(
	 ID INT IDENTITY(1,1)
	,BatchStart DATETIME
	,LoopNumber INT
	,DeleteDate DATETIME
	,StartTime DATETIME
	,FinishTime DATETIME
	)
END
 
/* find the number of loops we need to do based upon @DeleteRange and @retain */
SET @SQL = N'SELECT @loopOUT = DATEDIFF(' + @DeleteRange + ', MIN(backup_start_date),GETDATE()) FROM [msdb].[dbo].[backupset]'
EXEC SP_EXECUTESQL @SQL, N'@DeleteRange nvarchar(100), @loopOUT int OUTPUT', @DeleteRange = @DeleteRange, @loopOUT = @loop OUTPUT
 
/* delete backup history in batches */
WHILE @loop >= @retain
BEGIN
 
	SET @SQL = N'SELECT @DateOUT = DATEADD(' + @DeleteRange + ',-' + CONVERT(VARCHAR,@loop) + ',GETDATE())'
	EXEC SP_EXECUTESQL @SQL, N'@DeleteRange nvarchar(100), @DateOUT datetime OUTPUT', @DeleteRange = @DeleteRange, @DateOUT = @DeleteDate OUTPUT
 
    INSERT INTO msdb.dbo.BackupDeleteLog (BatchStart, LoopNumber, DeleteDate, StartTime)
    SELECT @BatchStart, @loop, @DeleteDate, GETDATE()
 
    --EXEC sp_delete_backuphistory @DeleteDate
	SELECT @DeleteDate
 
    UPDATE msdb.dbo.BackupDeleteLog SET FinishTime = GETDATE() WHERE LoopNumber = @loop AND BatchStart = @BatchStart
 
    SET @loop = @loop - 1
 
END
 
/* check what we've just deleted */
SELECT * FROM msdb.dbo.BackupDeleteLog WHERE BatchStart = @BatchStart

Now, I’ve intentionally commented out the actual delete so that you can see that the loops are working correctly and make sure everything looks good before you go ahead and actually delete that data.

As always, do anything like this in a development environment before running against production. You need to know what you’re running and the effects it will have on your system.

Let’s go ahead and run our query that shows tables by space used and any free space available in them:

Source code

USE[msdb]
GO
SELECT 
     t.name TableName
    ,s.name SchemaName
    ,p.ROWS TotalRows
    ,CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalMB
    ,CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.partitions p ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name IN 
('backupset','backupfile','backupfilegroup','backupmediafamily','backupmediaset','restorefile','restorefilegroup','restorehistory')
GROUP BY t.name, s.name, p.ROWS
ORDER BY TotalMB DESC, t.name

They likely will not have reduced by much in total size but you should notice plenty of free space now. To take this space back from the table we’ll want to rebuild the clustered indexes. The simplest way of doing this is to navigate to the table in question, find the clustered index, right click, and select rebuild;

And remember, if you ever want to see the code that is running behind the scenes, you can click the Script button;

It’s worth doing this in this instance. If you’re on Enterprise Edition, you can do an online index rebuild, so make sure you have ONLINE = ON.

Once your tables are nicely rebuilt, you’ll want to consider shrinking the database to reclaim that lost space. Rebuilding a DB is not something that you should be doing regularly (if you do, then there are other issues that need resolving), but as a one-off as part of a clean up task such as this, I’d consider it acceptable.

To do this, right click MSDB, tasks, shrink, database and click OK.

Again, this is something that I’d recommend doing out of hours or during a maintenance window, and please check this in your development environment before doing in production. This can be a fairly long-running query depending upon the size of your msdb and your underlying hardware.

Prevention is better than a cure

You don’t want to have to go through all this again, do you? Thankfully there’s a simple answer to this and we’ve already got everything we need in order to make sure this doesn’t happen again. We just need to wrap up the code above into a nice little package and create a SQL Agent job.

So go ahead and make a new SQL Agent job that runs on whatever schedule you like. That could be daily, could be weekly, could be monthly, it’s your call. Paste in the code that we’ve used above to delete the data, make sure our parameters are correct and agreed with the business, then go ahead and switch it on.

The only change to the code above I’d recommend is to make sure that we’re cleaning up after ourselves. We can replace the final SELECT with this:

Source code

DELETE msdb.dbo.BackupDeleteLog WHERE BatchStart < DATEADD(MONTH,12,GETDATE())

This will delete our backup deletion after a year; feel free to modify to your own requirements. We should do this for exactly the same reason that we’re deleting backup history: our data will just stay around forever otherwise, and our future selves are likely going to need to deal with that. No reason to make more work for ourselves!

If you’re concerned that there may be other maintenance that SQL should do for you but might not, we here at SSG recommend Ola Hallengren’s Maintenance Solution. This is an excellent set of scripts that will create various jobs to take care of your server. Go here and follow the instructions to download MaintenanceSolution.sql. These scripts are great for doing everything from backups to database integrity checks as well as cleaning up system tables (like the darned backup history jobs tables).

I hope this has been useful. Feel free to comment below or reach out if you have any questions about how to clear backup history.