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
