SQLTeam.com | Weblogs | Forums

Sql history retention logs


#1

Do you have to really have to delete SQL history retention logs?


#2

Backup History in MSDB?

Yes

Assuming 3 system databases backed up once a day and 5 application databases with log backups ever 10 minutes:

Daily = 3 + 5 (full) + 5 x (24 hours x 60 mins / 10 min interval) = 728 records.
Annually = 265,720

MSDB stores at least one row per backup in each of 4 tables, plus at least 2 in [backupfile] - so number of backups x 6 - i.e. 1,594,320

I don't know if it is still the case if it is still rubbish, but the provided "sp_delete_backuphistory" used to have dreadful performance. It would take, literally, days to clean out 6 months worth of history records if there were, say, 5,000,000 rows stored (as a consequence of its lousy performance I use my own home-made process instead). Running sp_delete_backuphistory daily, to just retain, say, 3 months worth of history means that the system is in equilibrium and the deletion of stale data happens in an acceptable timeframe without significant impact on other uses of the server.