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
