Ive found this on numerous occasions and decided not to be lazy this time around and investigate why it takes so long to drop a database when the ‘Delete backup and Restore History Information for databases’ checkbox is ticked.
When thinking about it logically, when purging historical data with a where criteria against a set of tables in any database, the task would be faster when you have indexes in place. this is exactly the scenario which causes the GUI to seem like its hanging. However, if you check sys.dm_exec_requests you will be able to see CPU and IO actively moving and a DELETE command against the msdb, in the form of:
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'MyDB'
Running the following will show you which indexes are present on the msdb database as standard:
SELECT t.name as [Table], ind.name as [Index], col.name as [Column], ind.type_desc [Index Type]
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
…Only 4 indexes are present as standard in the msdb database.
This problem can be alleviated by adding a set of indexes to some key tables in the msdb database.
This script is taken from Here
/************************************************************************ * * * Title: msdb index creation * * Author: Geoff N. Hiten * * Purpose: Index msdb database * * Date: 12/12/2005 * * Modifications: * ************************************************************************/ use msdb
go
--backupset Create index IX_backupset_backup_set_id on backupset(backup_set_id) go
Create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid) go
Create index IX_backupset_media_set_id on backupset(media_set_id) go
Create index IX_backupset_backup_finish_date on backupset(backup_finish_date) go
Create index IX_backupset_backup_start_date on backupset(backup_start_date) go
--backupmediaset Create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id) go
--backupfile Create index IX_backupfile_backup_set_id on backupfile(backup_set_id) go
--backupmediafamily Create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id) go
--restorehistory Create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id) go
Create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id) go
--restorefile Create index IX_restorefile_restore_history_id on restorefile(restore_history_id) go
--restorefilegroup Create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id) go
This small addition will speed up the deletion process completely and make sure the GUI doesnt hang waiting for a background delete against the msdb to complete.
Useful scripts
I found that deleting backup history was slowing down, so i did the following:
USE msdb; GO
UPDATE STATISTICS backupfile; GO
UPDATE STATISTICS backupmediafamily; GO
UPDATE STATISTICS backupmediaset; GO
UPDATE STATISTICS backupset; GO
UPDATE STATISTICS restorefile; GO
UPDATE STATISTICS restorefilegroup; GO
UPDATE STATISTICS restorehistory; GO
And also added the following index, after looking at the estimated execution plan:
Create index IX_backupset_database_name on backupset(database_name) go
Some general queries:
--Find count of records older than certain date select count(*) from backupset with (nolock) where backup_start_date < '8/15/2009' --with database name select * from backupset with (nolock) where backup_start_date < '08/15/2011' and database_name='MyDB' --Find databases which have history data but no longer attached to instance select distinct(database_name) from backupset with (nolock) where database_name not in (select name from sys.databases) --Purge history older than certain date sp_delete_backuphistory @oldest_date='08/15/2009' --Purge history for a particular DB EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'myTest'
Mohsin's DBA Blog 