Purging Backup History

Posted on 2009-12-26
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
    LVL 4

    Expert Comment

    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.
    LVL 1

    Author Comment

    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.
    LVL 1

    Accepted Solution

    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
    LVL 6

    Expert Comment

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now