Just a while ago, one of the colleagues from my previous employer posted a question on #sqlhelp to know how backup related records in MSDB database get purged. I gave him this screenshot of SQL Server Agent properties which controls the Job History in MSDB.
I was about to tweet about sp_delete_backuphistory stored procedure which can be used to prune the backup/restore history in MSDB. But someone else did it before I could. This stored procedure reminded me of one of the questions I had come across in one of the forums, where the user trying to drop the database using SSMS and SSMS would just hang. After a few responses he informed that he had selected “Delete backup and restore history information….” option while dropping the database.
There were no processes which were blocking this still the database would never get dropped. He had several thousand records of backup history for this database. The drop database session was progressing but it was really slow because it was waiting for the “Delete backup and restore….” transaction to complete. You may be surprised to know the reason for this. No indexes/foreign keys are there on the tables related to Backup/Restore history tables! sp_delete_backuphistory stored procedure deletes data from the following tables in MSDB.
Run this query on MSDB and you would be surprised to know that there are only 4 indexes present for those 8 tables.
SELECT t.name as [TableName], ind.name as [IndexName], col.name as [ColumnName], ind.type_desc [IndexType] from sys.indexes ind inner join sys.index_columns ic on ind.object_id = ic. object_id and ind.index_id = ic. index_id inner join sys.columns col on ic.object_id = col.object_id and ic.column_id = col.column_id inner join sys.tables t on ind.object_id = t. object_id where ind.is_unique = 0 and ind. is_unique_constraint = 0 and t.name in ( 'backupfile', 'backupfilegroup', 'backupmediafamily', 'backupmediaset', 'backupset', 'restorefile', 'restorefilegroup', 'restorehistory' ) order by t .name, ind.name
Since there are no proper indexes on these tables, the stored procedure would obviously take a long time to complete. What is the fix? Create the indexes on the columns that are being referred by the sp_delete_backuphistory stored procedure. Geoff N. Hiten had written a good script which does this job. The script can be downloaded from here. The user created the indexes on MSDB using this script and he was in for a surprise. The backup job history pruning completed quickly and the database got dropped in a few seconds.
If you are facing similar issues, please go ahead and create those indexes on MSDB tables. Even if you are not affected by this yet, it doesn’t hurt to keep these indexes on. You never know when you need them 😉