View Thread
 Print Thread
Slow DB Deletion - Add missing MSDB indexes
admin
Ever tried deleting a database with the "Delete backup and restore history information for databases" ticked, only to find it takes hours? This is because it essentially does table scans on the MSDB database, create the indexes below before deleting and you'll be laughing.

Quote

CREATE NONCLUSTERED INDEX [IX_backupset_database_name_backup_set_id]
ON [dbo].[backupset] ([database_name]) INCLUDE ([backup_set_id])
CREATE NONCLUSTERED INDEX [IX_backupset_database_name_media_set_id]
ON [dbo].[backupset] ([database_name]) INCLUDE ([media_set_id])
CREATE NONCLUSTERED INDEX [_ped_backupset_media_set_id]
ON [dbo].[backupset] ([media_set_id])


Credit to Peter Edenharter: http://connect.microsoft.com/SQLServe...-intensive
Jordon Pilling | Heavencore Administrator
 
Jump to Forum