troubleshooting Question

Purging Backup History

Avatar of apitech
apitech asked on
Microsoft SQL Server
4 Comments1 Solution686 ViewsLast Modified:
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 = ' +
        convert(varchar(10),@cutoff_date ,121)
while   1 = 1
        begin
        set @purge_date = null
        -- Find date of oldest backup set
        select
               @purge_date = dateadd(dd,datediff(dd,0,min(backup_finish_date))+1,0)
        from
               msdb.dbo.backupset
        where
               backup_finish_date <= @cutoff_date
        if @purge_date is null or @purge_date > @cutoff_date
               begin
               print 'Purge backup history complete through '+
                       convert(varchar(10),@cutoff_date ,121)
               break
               end
        print   char(10)+char(13)+'Purging backup history before ' +
               convert(varchar(10),@purge_date,121) +char(10)+char(13)
        select
               [Backup Sets to be Deleted Count ] = count(*)
        from
               msdb.dbo.backupset
        where
               backup_finish_date < @purge_date
        exec msdb.dbo.sp_delete_backuphistory @purge_date
        end  -- End While
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros