Pruning backup history taking too long to complete? Read on

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.

  • backupfile
  • backupfilegroup
  • backupmediafamily
  • backupmediaset
  • backupset
  • restorefile
  • restorefilegroup
  • restorehistory

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 😉

5 thoughts on “Pruning backup history taking too long to complete? Read on

  1. Pingback: Tweets that mention Pruning backup history taking too long to complete? | SQL Server DBA Diaries of Pradeep Adiga -- Topsy.com

    1. PradeepAdiga Post author

      Thanks Amit for the useful link! But to improve the performance of these SPs indexes would certainly help. Till the additional indexes get ms_shipped, this helps to survive the crisis (surely at the cost of MS Support).

Comments are closed.