Huge MSDB database

disk-spacemsdbtruncate

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!

Best Answer

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)
WAITFOR DELAY '00:00:05'
print 'IX_backupfile_backup_set_id..'

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

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

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


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

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


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

Print 'End of Maintenance Reindex..'



--delete records    

print 'DEL restorefile..' 
if exists (select * from msdb.dbo.sysobjects where name = 'restorefile')    
begin 
    
    DELETE top (@Batch) FROM msdb..restorefile
    FROM msdb..restorefile rf with (nolock)
        INNER JOIN msdb..restorehistory rh with (nolock) ON rf.restore_history_id = rh.restore_history_id
        INNER JOIN msdb..backupset bs with (nolock) ON rh.backup_set_id = bs.backup_set_id
    WHERE bs.backup_finish_date < @DeleteDate 
    
    
    while @@rowcount <> 0    
    begin    
    
        DELETE top (@Batch) FROM msdb..restorefile
        FROM msdb..restorefile rf with (nolock)
            INNER JOIN msdb..restorehistory rh with (nolock) ON rf.restore_history_id = rh.restore_history_id
            INNER JOIN msdb..backupset bs with (nolock) ON rh.backup_set_id = bs.backup_set_id
        WHERE bs.backup_finish_date < @DeleteDate 
        
    end    
end    



print 'DEL restorefilegroup..'  
if exists (select * from msdb.dbo.sysobjects where name = 'restorefilegroup')    
begin 
    
    DELETE top (@Batch) FROM msdb..restorefilegroup
    FROM msdb..restorefilegroup rfg with (nolock)
        INNER JOIN msdb..restorehistory rh with (nolock) ON rfg.restore_history_id = rh.restore_history_id
        INNER JOIN msdb..backupset bs with (nolock) ON rh.backup_set_id = bs.backup_set_id
    WHERE bs.backup_finish_date  < @DeleteDate 

    
    while @@rowcount <> 0    
    begin    
    
        DELETE top (@Batch) FROM msdb..restorefilegroup
        FROM msdb..restorefilegroup rfg with (nolock)
            INNER JOIN msdb..restorehistory rh with (nolock) ON rfg.restore_history_id = rh.restore_history_id
            INNER JOIN msdb..backupset bs with (nolock) ON rh.backup_set_id = bs.backup_set_id
        WHERE bs.backup_finish_date  < @DeleteDate  
    End
End 
    
    
    
print 'Temp table ..'   
SELECT media_set_id, backup_finish_date
    INTO #Temp 
FROM msdb..backupset with (nolock)
WHERE backup_finish_date  < @DeleteDate 


    


print 'DEL backupfile..' 
if exists (select * from msdb.dbo.sysobjects where name = 'backupfile')    
begin 
    
    DELETE top (@Batch) FROM msdb..backupfile
    FROM msdb..backupfile bf with (nolock)
        INNER JOIN msdb..backupset bs with (nolock) ON bf.backup_set_id = bs.backup_set_id
        INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
    WHERE bs.backup_finish_date  < @DeleteDate 
    
    
    while @@rowcount <> 0    
    begin    
    
        DELETE top (@Batch) FROM msdb..backupfile
        FROM msdb..backupfile bf with (nolock)
            INNER JOIN msdb..backupset bs with (nolock) ON bf.backup_set_id = bs.backup_set_id
            INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
        WHERE bs.backup_finish_date  < @DeleteDate      
    End
End 



print 'DEL backupmediafamily..' 
if exists (select * from msdb.dbo.sysobjects where name = 'backupmediafamily')    
begin 
    
    DELETE top (@Batch) FROM msdb..backupmediafamily
    FROM msdb..backupmediafamily bmf with (nolock)
        INNER JOIN msdb..backupmediaset bms with (nolock) ON bmf.media_set_id = bms.media_set_id
        INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
    
    
    while @@rowcount <> 0    
    begin    
        
        DELETE top (@Batch) FROM msdb..backupmediafamily
        FROM msdb..backupmediafamily bmf with (nolock)
            INNER JOIN msdb..backupmediaset bms with (nolock) ON bmf.media_set_id = bms.media_set_id
            INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
    End
End 



print 'DEL backupfilegroup..' 
if exists (select * from msdb.dbo.sysobjects where name = 'backupfilegroup')    
begin 
    
    Delete top (@Batch) FROM msdb..backupfilegroup
        FROM msdb..backupfilegroup bfg with (nolock)
        INNER JOIN msdb..backupset bs with (nolock) ON bfg.backup_set_id = bs.backup_set_id
        INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
    
    
    while @@rowcount <> 0    
    begin    
    
        Delete top (@Batch) FROM msdb..backupfilegroup
        FROM msdb..backupfilegroup bfg with (nolock)
            INNER JOIN msdb..backupset bs with (nolock) ON bfg.backup_set_id = bs.backup_set_id
            INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id

    End
End 
        
    

print 'DEL restorehistory..' 
if exists (select * from msdb.dbo.sysobjects where name = 'restorehistory')    
begin       
    DELETE top (@Batch) FROM msdb..restorehistory
    FROM msdb..restorehistory rh with (nolock)
        INNER JOIN msdb..backupset bs with (nolock) ON rh.backup_set_id = bs.backup_set_id
    WHERE bs.backup_finish_date  < @DeleteDate 
    
    
    while @@rowcount <> 0    
    begin    
    
        DELETE top (@Batch) FROM msdb..restorehistory
        FROM msdb..restorehistory rh with (nolock)
            INNER JOIN msdb..backupset bs with (nolock) ON rh.backup_set_id = bs.backup_set_id
        WHERE bs.backup_finish_date  < @DeleteDate 
    
    End
End 
    
    
print 'DEL backupset..' 
if exists (select * from msdb.dbo.sysobjects where name = 'backupset')    
begin   

    DELETE top (@Batch) FROM msdb..backupset
    FROM msdb..backupset bs with (nolock)
        INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
        
    while @@rowcount <> 0    
    begin    
    
        DELETE top (@Batch) FROM msdb..backupset
        FROM msdb..backupset bs with (nolock)
            INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
    
    End
End 
    

print 'DEL backupmediaset..' 
if exists (select * from msdb.dbo.sysobjects where name = 'backupmediaset')    
begin   

    DELETE top (@Batch) FROM msdb..backupmediaset
    FROM msdb..backupmediaset bms with (nolock)
        INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
        
    while @@rowcount <> 0    
    begin    
    
        DELETE top (@Batch) FROM msdb..backupmediaset
        FROM msdb..backupmediaset bms with (nolock)
            INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
    
    End
End 
    

DROP TABLE #Temp



print 'Update Statistic on msdb'
-- Updates the statistics for all tables in the database. 
EXEC sp_updatestats
go
Related Question