Cleaning up Backup and Restore History Logs in MSDB

Cleaning up Backup and Restore History Logs in MSDB

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
Sometimes, there are cases, where it is reported to me that the MSDB database is huge and needs to be shrinked again back to “normal” sizes.

One case which affects the size of MSDB, is the backup history. Every time a database backup/restore operation takes place in SQL Server, the relevant information is kept into certain system tables in the MSDB database.

SQL Server stores database backup and restoration information into the following tables that exist in the MSDB database:

  • backupfile
  • backupfilegroup
  • restorefile
  • restorefilegroup
  • restorehistory
  • backupset

Let’s examine an example for better understanding the process.

In this example I am using a database in SQL Server 2008 called “DEMODB2008“. To this end, I performed some backup and restore operations for allowing us to check out the data stored in MSDB.

The following queries retrieve information regarding the backup and restore operations that took place on the specific database:

— This query returns information about the backup set
SELECT * FROM msdb.dbo.backupset
WHERE [database_name]=’DEMODB2008′

— This query returns information about the backup files including 
— the logical and physical file names, drive, etc.
SELECT bf.*
FROM msdb.dbo.backupfile bf
INNER JOIN msdb.dbo.backupset bs
ON bf.[backup_set_id]=bs.[backup_set_id]
AND bs.[database_name]=’DEMODB2008′

— This query returns information about the filegroups that were backed up.
SELECT bg.*
FROM msdb.dbo.backupfilegroup bg
INNER JOIN msdb.dbo.backupset bs
ON bg.[backup_set_id]=bs.[backup_set_id]
AND bs.[database_name]=’DEMODB2008′

— This query returns information about when a database was restored.
SELECT * FROM msdb.dbo.restorehistory
WHERE [destination_database_name]=’DEMODB2008′

— This query returns information about the physical files involved in the restoration process.
SELECT rf.*
FROM msdb.dbo.restorefile rf
INNER JOIN msdb.dbo.restorehistory rh
ON rf.[restore_history_id]=rh.[restore_history_id]
AND rh.[destination_database_name]=’DEMODB2008′
— This query returns information about the restored filegroups.
SELECT rg.* FROM msdb.dbo.restorefilegroup rg
INNER JOIN msdb.dbo.restorehistory rh
ON rg.[restore_history_id]=rh.[restore_history_id]
AND rh.[destination_database_name]=’DEMODB2008′

In the case where the MSDB database is huge you can try removing any unnecessary backup and restore history logs.

Instead of manually deleting these logs from the above tables, you can use a SQL Server system stored procedure instead.

This stored procedure is called “sp_delete_backuphistory” and exists in the MSDB system database.

The usage for the above stored procedure is:

sp_delete_backuphistory [ @oldest_date = ] ‘oldest_date’

Example:

exec msdb.dbo.sp_delete_backuphistory ‘2009-01-01’

The above command will clean up the backup and restore history logs up to 01/01/2009.

For more information you can visit SQL Server Books Online.


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.