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 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s