Cleaning Up Backup History

Every time there is a backup or restore action, SQL Server stores a record in msdb. In my setup I perform quite a few log backups every day. If I allow the logs to continue to grow I eventually start to see performance degradation on the backup and the restore sides. You can clean up this history in a couple of ways. One way is to set up a Maintenance Plan that cleans up the history. I use this on a day to day basis. Sometimes I may need to remove history records manually so I use this little snippet.

USE msdb
GO
DECLARE @OldestDateToKeep DATETIME
SET @OldestDateToKeep = CONVERT(VARCHAR(10), DATEADD(dd, -30, GETDATE()), 101)
EXEC sp_delete_backuphistory @OldestDateToKeep
GO
Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *