Tag Archives: sp_delete_backuphistory

SQL Server: Purging msdb History Tables to Control msdb Database Growth

From time to time, I see the following question posted on various SQL Server forums by different users asking why the msdb database on their SQL Server instance is too big. As we all know, msdb is a system database that supports SQL Server Agent service, and stores all the information and history about job scheduling, job history, backup and restores, service broker, database mail and other maintenance activity. It is very important to regularly purge the following msdb history tables, because without regular maintenance, these system tables can grow to very large sizes, which results in unexpected growth of msdb database and slow performance during backup and restores and database mail operations.

Checkout my here, in which discussed how we can purge msdb backup, job, and database mail history tables.

This article is published on SSWUG.org.

Advertisement

Purging MSDB Backup and Restore History Tables

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 stored procedure:

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.