Missed to regularly purge MSDB!! , what you have to consider prior to start ?

In the last couple of days I had to deal with these number for a MSDB db hasn't purged since 3 years !!!! yeah 3 years , so I had to do many tasks out of the normal tasks ,

let me give you brief about the figures

1. backupFile --- 10 Million rows
2. backupFilegroup --- 6 Million rows
3. backupmediafamily--- 4 Million rows
4. backupmediaset--- 4 Million rows
5. backupset--- 4 million

the total size of the MSDB is 11 GB ,

the fact is , if you start purging the historical backup data you will not be able to perform any type of backup operations for hours if it hasn't take days , in addition to growth of the log file of MSDB dbs , so I had a practical experience I would like to share to do so in a very smooth way as following :

1. extract the whole data in the list of tables above to another SQL instance plus these tables

• restorefile
• restorefilegroup
• restore history

2.apply missing indexes

 Create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id)
go

 

Create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id)
go

 

CREATE NONCLUSTERED INDEX [IX_pro_Del]

ON [dbo].[backupset] ([database_name])

INCLUDE ([backup_set_id])

GO

 

CREATE NONCLUSTERED INDEX []

ON [dbo].[backupset] ([database_name])

INCLUDE ([media_set_id])

GO

 

last step start delete per database
EXEC sp_delete_database_backuphistory 'you database Name'
GO
once you reach a reasonable number of transactions in above listed tables you can get back to normal operation and delete by date

what you have done on the test instance can be applied to your production without any interruption to your operation (backup and  size of log file)

let me know if anything goes wrong ... Enjoy :)