Housekeeping backup and restore history
Every backup and restore event is logged in history tables in msdb and these tables can get out of hand, especially if you're using log shipping. Here's one way to sort it out that will catch up with the backlog and then keep your history tables trimmed.
This code will delete the oldest days worth of history as long as it's more than 30 days ago:
--Delete earliest day of backup and restore history if it's older than 30 days.
--This way you can clear a long history a bit at a time.
use msdb
go
set nocount on
declare @oldest_backup_date smalldatetime
, @oldest_restore_date smalldatetime
, @oldest_date smalldatetime
--Find the oldest backup date (can be null)
select @oldest_backup_date = min(backup_finish_date)
from backupset with (nolock)
where backup_finish_date is not null
set @oldest_backup_date = isnull(@oldest_backup_date, '01-JUN-2079')
--Find the oldest restore date (can be null)
select @oldest_restore_date = min(restore_date)
from restorehistory with (nolock)
where restore_date is not null
set @oldest_restore_date = isnull(@oldest_restore_date, '01-JUN-2079')
--Determine the earlier of the two dates
if @oldest_restore_date < @oldest_backup_date
set @oldest_date = @oldest_restore_date
else
set @oldest_date = @oldest_backup_date
print 'Oldest date is ' + convert(varchar(10), @oldest_date, 103)
--If it's more than 30 days ago delete it
if @oldest_date >= dateadd(day, -30, getdate())
begin
print 'no action'
end
else
begin
print 'deleting history'
--sp_delete_backuphistory deletes everything earlier than @oldest_date.
--Add 1 day otherwise it won't delete anything.
set @oldest_date = dateadd(day, 1, @oldest_date)
exec sp_delete_backuphistory @oldest_date
end
Before you implement it though, I advise you to add some indexes to msdb. Normally I wouldn't screw around with system tables but I made an exception here. These tables are poorly indexed and adding some indexes makes so much difference. I found this post on Geoff Hiten's blog and brought the script up to date:
-- msdb performance indexes for SQL Server 2005
-- History:
-- 12/12/2005 Geoff N. Hiten Created (http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx).
-- 06/07/2009 David Wimbush Commented out indexes that Microsoft have implemented.
-- Added an index on restorehistory restore_date.
use msdb
go
--backupset
--implemented by Microsoft
--create index IX_backupset_backup_set_id on backupset(backup_set_id)
--go
--implemented by Microsoft
--create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid)
--go
create index IX_backupset_media_set_id on backupset(media_set_id)
go
create index IX_backupset_backup_finish_date on backupset(backup_finish_date)
go
create index IX_backupset_backup_start_date on backupset(backup_start_date)
go
--backupmediaset
--implemented by Microsoft
--create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id)
--go
--backupfile
--implemented by Microsoft
--create index IX_backupfile_backup_set_id on backupfile(backup_set_id)
--go
--backupmediafamily
--implemented by Microsoft
--create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id)
--go
--restorehistory
--implemented by Microsoft
--create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id)
--go
--implemented by Microsoft
--create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id)
--go
--NEW:
create index IX_restorehistory_restore_date on restorehistory(restore_date)
go
--restorefile
create index IX_restorefile_restore_history_id on restorefile(restore_history_id)
go
--restorefilegroup
create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id)
go