Purging Backup History
Posted on 2009-12-26
In SQL Server 2000 with service pack 4, I am running the script at the end of this posting to delete backup history.
The reason that I am running this huge script against the msdb database in the first place is because the msdb database has been approaching 8GB in size and has grown by a sizable margin daily. I need to reduce its size.
I started running this script at 2:30 PM EST, and it is still running at the time of this posting (almost 8 hours)! I am aware that this script is supposed to take a long time, and there are 86 databases in this environment. Not to mention, there are over two years of backup history that has never been previously deleted.
But, is it suppposed to take this long? I really hope that this finishes before Monday AM.
I noticed that, while this has been running, the size of msdb has remained unchanged. Is that normal?
In order to speed up this process, can I run the "EXEC SP_DELETE_BACKUPHISTORY @backup_date" script based on a short-term date range? Or, would that not do any good?
Currently, I have disabled all backup jobs since no one is working in the system this weekend anyway. If this script is still running come Monday, I suppose that I will let it run, change the recovery model for all databases to Simple, and keep the backup jobs including transaction log backup jobs disabled. There's no harm in having this script continue to run while pepole are in it, is there? Or, should I stop it if it keeps running like this?
set nocount on
declare @purge_date datetime
declare @cutoff_date datetime
-- Set purge cutoff to 30 days back
set @cutoff_date = dateadd(dd,datediff(dd,0,getdate())-30,0)
print 'Purge backup history before Cutoff Date = ' +
while 1 = 1
set @purge_date = null
-- Find date of oldest backup set
@purge_date = dateadd(dd,datediff(dd,0,min(backup_finish_date))+1,0)
backup_finish_date <= @cutoff_date
if @purge_date is null or @purge_date > @cutoff_date
print 'Purge backup history complete through '+
print char(10)+char(13)+'Purging backup history before ' +
[Backup Sets to be Deleted Count ] = count(*)
backup_finish_date < @purge_date
exec msdb.dbo.sp_delete_backuphistory @purge_date
end -- End While