SQL Server: Delete Backup History to reduce MSDB database size

SQL Server keeps a track of the backup history of your server, in the msdb database. Every time a backup or restore operation occurs on your database, additional rows are added to the backup and restore history tables. If you do not keep a check on this, you will find your msdb database growing over time.

SQL Server provides the sp_delete_backuphistory database engine stored procedure which makes it very simple to delete history that is older than the specified date. Here’s how to use this stored procedure to delete backup history that is older than Jan 31, 2011, 12:00 A.M. in the backup and restore history tables.

Similarly if you want to automatically delete records that is say 2 months old, create a stored procedure that calculates the date and executes the sp_delete_backuphistory procedure

Here's the same query for you to try out:

CREATE PROCEDURE [dbo].[DeleteBackupHistory]
AS
 
DECLARE @BckDate DATETIME
SET @BckDate = CONVERT(varchar(10), DATEADD(dd, -60, GETDATE()), 101)

EXEC sp_delete_backuphistory @BckDate

Now whenever you want to delete records 2 months prior to the current date, just call the DeleteBackupHistory stored procedure.

Similarly, you may also want to look at the sp_purge_jobhistory and sp_maintplan_delete_log to remove other history information and keep your msdb database from growing over time.

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook