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.

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: