Huge MSDB database

I have a very large MSDB database and have been working on clearing this out and setting up maintenance tasks - something my predecessor should have done years ago!

So far I've managed to truncate the sysmaintplan_logdetail and sysmaintplan_log files.

Now I'm hoping to look at some of the backup databases - the backupfile table is over 5Gb.

I tried to use the SP sp_delete_backuphistory but this filled my MSDB transaction log and almost killed the server...

Are there any other ways to clear this and shrink the file without causing any damage?

For information my MSDB data file is still 20Gb, used 13Gb. The log in 500Mb, used 6Mb.

Any help would be appreciated - I'm no DBA!

I tried to use the SP sp_delete_backuphistory but this filled my MSDB transaction log and almost killed the server...

Are there any other ways to clear this and shrink the file without causing any damage?

Do it in batches.

If your msdb is huge, then after running the script, to release unused space, I would recommend you to shrink your msdb (Yes shrink it and then once the space is released, you can do a reorg/rebuild and update stats use OLA's scripts - This shrink will be a one time thing to do- so that you can release the unused space).

Make sure that you schedule the below script to run on a frequent basis depending on how busy your servers is (in terms of frequency of jobs, logshipping is running or not and the use of database mail + the backups and restores happening on the server as they all log into MSDB)

```
use msdb
/* Author : Kin
Purpose: For dba.stackexchange.com - Trim down msdb
*/

-- Declaration
DECLARE @DeleteDate datetime
DECLARE @DaysToRetain int
DECLARE @Batch int

set @DaysToRetain = 30
set @Batch = 5000
set @DeleteDate = convert(datetime,convert(varchar,getdate()-@DaysToRetain,101),101)

-- ----------------
-- Index creation
-- ----------------
-- backupset
-- ----------------
Print 'Index Creation..'

if not exists (select * from msdb..sysindexes where name = 'IX_backupset_backup_set_uuid')
begin
Create NONCLUSTERED index IX_backupset_backup_set_uuid on backupset(backup_set_uuid)
end

if not exists (select * from msdb..sysindexes where name = 'IX_backupset_media_set_id')
begin
Create NONCLUSTERED index IX_backupset_media_set_id on backupset(media_set_id)
end

if not exists (select * from msdb..sysindexes where name = 'IX_backupset_backup_finish_date')begin
Create NONCLUSTERED index IX_backupset_backup_finish_date on backupset(backup_finish_date)
end

if not exists (select * from msdb..sysindexes where name = 'IX_backupset_backup_start_date')
begin
Create NONCLUSTERED index IX_backupset_backup_start_date on backupset(backup_start_date)
end

-- ------------
-- backupfile
-- ------------
if not exists (select * from msdb..sysindexes where name = 'IX_backupfile_backup_set_id')
begin
Create NONCLUSTERED index IX_backupfile_backup_set_id on backupfile(backup_set_id)
end

-- -------------------
-- backupmediafamily
-- -------------------
if not exists (select * from msdb..sysindexes where name = 'IX_backupmediafamily_media_set_id')
begin
Create NONCLUSTERED index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id)
end

-- -------------------
-- backupfilegroup
-- -------------------
if not exists (select * from msdb..sysindexes where name = 'IX_backupfilegroup_backup_set_id')
begin
Create NONCLUSTERED index IX_backupfilegroup_backup_set_id on backupfilegroup(backup_set_id)
end

-- ----------------
-- restorehistory
-- ----------------
if not exists (select * from msdb..sysindexes where name = 'IX_restorehistory_restore_history_id')
begin
Create NONCLUSTERED index IX_restorehistory_restore_history_id on restorehistory(restore_history_id)
end

if not exists (select * from msdb..sysindexes where name = 'IX_restorehistory_backup_set_id')
begin
Create NONCLUSTERED index IX_restorehistory_backup_set_id on restorehistory(backup_set_id)
end

-- -------------
-- restorefile
-- -------------
if not exists (select * from msdb..sysindexes where name = 'IX_restorefile_restore_history_id')
begin
Create NONCLUSTERED index IX_restorefile_restore_history_id on restorefile(restore_history_id)
end

-- ------------------
-- restorefilegroup
-- ------------------
if not exists (select * from msdb..sysindexes where name = 'IX_restorefilegroup_restore_history_id')
begin
Create NONCLUSTERED index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id)
end

Print 'End of Index Creation..'

-- ------------------------------
-- Maintenance before deletion
-- ------------------------------
-- Reindex
-- ------------------------------

-- ----------------
-- backupset
-- ----------------
Print 'Maintenance Reindex..'

ALTER INDEX [IX_backupset_backup_set_uuid] ON [msdb].[dbo].[backupset] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_backupset_backup_set_uuid..'

ALTER INDEX [IX_backupset_media_set_id] ON [msdb].[dbo].[backupset] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_backupset_media_set_id..'

ALTER INDEX [IX_backupset_backup_finish_date] ON [msdb].[dbo].[backupset] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_backupset_backup_finish_date..'

ALTER INDEX [IX_backupset_backup_start_date] ON [msdb].[dbo].[backupset] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_backupset_backup_start_date..'

-- ------------
-- backupfile
-- ------------
ALTER INDEX [IX_backupfile_backup_set_id] ON [msdb].[dbo].[backupfile] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFO

StackExchange Database Administrators Q#63755, answer score: 4