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.

sp_delete_backuphistory
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]
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.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

2 Responses to "SQL Server: Delete Backup History to reduce MSDB database size"
  1. Kevin admin said...
    March 29, 2011 at 3:31 AM

    You can also use a program to make SQL server backup like Handy Backup (http://www.handybackup.net)

  2. troubleshootingsql said...
    April 7, 2011 at 7:44 AM

    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.

    Reference:
    http://troubleshootingsql.com/2009/12/30/how-to-purge-msdb-history-using-t-sql-scripts/

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions