2

Have a server with regular db backups but strangely sp_delete_backuphistory has never been scheduled so msdb is bigger than expected. Backup history goes back 5 years in that table but only needs last 30 days of backup history. If we run sp_delete_backuphistory keeping only 30 days of history, is there any risk for the transaction log running out of space? MSDB is using simple recovery model. MSDB log file is currently 388544kb on C drive. C drive has 45gb free.

1 Answer 1

4

You may want to run the deletes in smaller chunks to avoid excessive log file growth for this one-time cleanup, particularly because your msdb database is on the C:\ drive.

If you have 5 years of data to clean up, the easiest way would likely be to simply call sp_delete_backuphistory several times, to clear out the backup history in 6 month or 1-year chunks.

For example:

EXEC sp_delete_backuphistory @oldest_date = '20150101'; --Jan 2015
EXEC sp_delete_backuphistory @oldest_date = '20160101'; --Jan 2016
EXEC sp_delete_backuphistory @oldest_date = '20170101'; --Jan 2017
EXEC sp_delete_backuphistory @oldest_date = '20180101'; --Jan 2018
EXEC sp_delete_backuphistory @oldest_date = '20190101'; --Jan 2019
EXEC sp_delete_backuphistory @oldest_date = '20200101'; --Jan 2020

Simply by running this and experimenting with the @oldest_date, you could pretty easily determine how quickly you can chunk through. Depending on the number of databases and frequency of backups, doing yearly chunks will likely be sufficient. If this is a one-server, one-time cleanup, this would be the method I use.

You could also script out the definition of sp_delete_backuphistory and create your own copy of it that does the cleanup more efficiently. Erik Darling has created an alternative that is available on GitHub distributed as part of my own DBA Database project. I would use this option for ongoing/long term cleanup, and if the problem is more pervasive than a single server needing a one-time cleanup.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.