Purging Backup History

Posted on 2009-12-26
Medium Priority
Last Modified: 2012-05-08
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
        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 '+
                       convert(varchar(10),@cutoff_date ,121)
        print   char(10)+char(13)+'Purging backup history before ' +
               convert(varchar(10),@purge_date,121) +char(10)+char(13)
               [Backup Sets to be Deleted Count ] = count(*)
               backup_finish_date < @purge_date
        exec msdb.dbo.sp_delete_backuphistory @purge_date
        end  -- End While
Question by:apitech
  • 2

Expert Comment

ID: 26126789
Having encoutered this before on SQL2005 I would suggest that you take a look at the SQL statement that is performing the delete in the SP then add some indexing to the tables to help the query. This will help speed up the search for the data. If this has been left for too long and the tables are huge another option is to purge the tables completely (truncate table) of data then ensure your maintenance scripts keep on top of the purging, but this is a last resort and should be used with caution on live systems for obvious reasons.

Author Comment

ID: 26126993
Well, it's still running as of 7AM on Sunday, and I don't know how to do indexes.

I guess that I just need to know now if there is any harm in hitting the stop button in Query Analyzer on that script that is running and just run the following a little at a time:

USE msdb
EXEC sp_delete_backuphistory 'oldest date'

I am not that familiar with the "deep stuff" of looking into specific msdb tables, etc.  I just need my other questions annswered and this new question answered.

Accepted Solution

apitech earned 0 total points
ID: 26127499
Disregard.....I went ahead and stopped that big script as it was taking too long.  I am now running EXEC SP_DELETE_BACKUPHISTORY 'oldest_date' one month at a time.  I hope this works

Expert Comment

ID: 26136800
The Stored Procedure "sp_delete_backuphistory" will not run that much faster if you did a lot of restore/backup tasks...

To make the cleaning of older backup/restore run a lot faster, consider a few extra indexes...

-- CREATE INDEX IDX_temp on restorefile ( restore_history_id )
-- CREATE INDEX IDX_temp on restorefilegroup ( restore_history_id )
-- CREATE INDEX IDX_temp on backupset ( backup_finish_date )
-- CREATE INDEX IDX_temp2 ON backupset ( media_set_id )

Hope this helps.


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question