March 28, 2011

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

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

CREATE PROCEDURE [dbo].[DeleteBackupHistory]
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, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal


Kevin admin said...

You can also use a program to make SQL server backup like Handy Backup (

troubleshootingsql said...

You need to put sp_purge_jobhistory along with that as well. The maintenance plan history cleanup task calls both these SPs. Without calling this, your job history pertaining to these backups would still be present if you were using SQL Agent backup jobs.