Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

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 SP:

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.


Comments

Leave a comment on the original post [basitaalishan.com, opens in a new window]

Loading comments...