Post reply

Problem Deleting Backup Data (sp_delete_backuphistory)

  • June 6, 2005 at 10:25 am

    #87902

    I've been runnning log shipping for nearly a year, and on several DB's.  My msdb database is now more than 4 Gigs and has about 5 million records in each of the backup tables.  Naturally, I have a similar situation on the standby server.

    I've been trying to delete data from these DB's using sp_delete_backuphistory.  For example, The min(backup_start_date)

    From backupset is 5/21/04.  I attempt to delete data using " EXEC sp_delete_backuphistory '06/1/2004' ".  I let the query run for 4.5 hours before stopping it and no data has been deleted.  Similarily, it took 4.5 hours to delete the first 17 hours of data from my primary server.  (No real processor load while running, log shipping stopped during this time.)

    These machines aren't huge, but should be plenty fast.  (Dual 2.4 GHz Xeon, 2 Gigs Ram, 10K SCSI w/hardware Raid.)

    Am I missing some switch somewhere?  Should I delete data from the backup tables myself.

    Any help appreciated!

     

    Matt

     

     

     

  • June 7, 2005 at 1:49 pm

    #563653

    USE MSDB

    DECLARE @backup_date DATETIME

    BEGIN

    set @backup_date=(select dateadd (dd, -15, getDate()))

    EXEC SP_DELETE_BACKUPHISTORY @backup_date

    END

    I have run this ( deleting backup history over 15 days old) and I have never had an issue - Granted the first one took a little time to run, since it was like 2 years of backup history that needed to be deleted. But I run this nightly and runs for a few seconds on all my DB servers.

  • June 8, 2005 at 10:11 pm

    #564066

    If your rowcounts are high for either the msdb..restorefile or msdb..restorefilegroup tables, try creating indexes on restore_history_id (they have no indexes as shipped). An index on msdb..backupset.media_set_id might also help.

    Any deletes of millions of rows will take forever though. You may want to select the rows you want to keep into temp tables, truncate the permanent ones, then re-populate them. Vik's suggestions seems like a good idea after you purge all the old junk.

    Funny how you said NO data was deleted, however. The sp does a commit after each cursor iteration. Perhaps something was blocking? You don't log-ship the msdb db do you? What are the rowcounts for each table, on each server?

  • July 14, 2005 at 4:20 am

    #574298

    As Fred Williams suugests, this speeds it up:

    CREATE

      INDEX [media_set_id] ON [dbo].[backupset] ([media_set_id])

    CREATE

      INDEX [restore_history_id] ON [dbo].[restorefile] ([restore_history_id])

    CREATE

      INDEX [restore_history_id] ON [dbo].[restorefilegroup] ([restore_history_id])

  • September 22, 2005 at 12:07 am

    #592079

    Hi to all you poor people coping with sp_delete_backuphistory

    I'm having the same issue. I created the indices and tried the script. Alas, it failed! The same thing happens as with Matt Martin: no data deleted.

    I'm now running a variation of vik's script. It just iterates back down from 600 days ago:

    USE MSDB
DECLARE @backup_date DATETIME
DECLARE @countback int
set @countback = 600
while @countback >= 320
BEGIN
 set @backup_date=(select dateadd (dd, @countback, getDate()))
 BEGIN TRAN DELE
 EXEC SP_DELETE_BACKUPHISTORY @backup_date
 COMMIT TRAN DELE
 set @countback = @countback - 20
END

    I do hope it'll end some time this year.

    It's a Quad-processor 3.0 GHz machine with 16 GB RAM running on Windows 2003 Enterprise with SQL 2000 Enterprise and 52 databases on the same instance. It has about 600 days of backup history logged.

    ....While I was writing this entry, the job failed on me with a boring:

    [Microsoft][ODBC SQL Server Driver]Timeout expired...

    Anybody else any ideas?

     


    __________________________________
    Searching the KB articles is like
    picking your nose. You never know
    what you'll find.

  • September 22, 2005 at 12:42 am

    #592088

    Well modified the script again and seems to be working so far. Changed from -20 to -2 to delete only two days worth of backup history, corrected a typo with the @countback variable and added the print @countback line.

    USE MSDB
DECLARE @backup_date DATETIME
DECLARE @countback int
set @countback = 600
while @countback >= 320
BEGIN
 print @countback
 set @backup_date=(select dateadd (dd, -@countback, getDate()))
 BEGIN TRAN DELE
 EXEC SP_DELETE_BACKUPHISTORY @backup_date
 COMMIT TRAN DELE
 set @countback = @countback - 2
END

    Will let you know what happens in the end.

    Regards,

    hot2use


    __________________________________
    Searching the KB articles is like
    picking your nose. You never know
    what you'll find.

  • September 22, 2005 at 3:17 am

    #592121

    Just to let you all know that it worked in the end.

    It took 2 hours and 30 Minutes to delete the backup history (from 600 to 320 days old) from the msdb database for 52 databases.

    Thanks vik for the script. Hope you don't mind my modifications?

    Regards, hot2use


    __________________________________
    Searching the KB articles is like
    picking your nose. You never know
    what you'll find.

  • September 22, 2005 at 8:15 am

    #592223

    I haven't dealt with this problem for a while, but I'm sure it's getting large yet again.

    In my case, I didn't care about retaining any backup history.  So, here's what I did:

    4 of the 7 tables that contain backup data do not have any foreign dependencies, so I simply truncated them.  That runs instantaniously.

    truncate table restorefilegroup

    truncate table restorefile

    truncate table backupfile

    truncate table backupmediafamily

    Then I delete data from the remaining 3 tables, in the correct order, with simple delete statements.

    1. delete from restorehistory where restore_date < '1/1/50'

    2. delete From backupset where backup_set_id < 200000000

    3. delete from backupmediaset where media_set_id < 200000000

    You may want to only delete a quater of the above tables at a time.  It's important to have the where clauses there, even if they specify all the data in the table.  For some reason, it's much, much faster than a delete without a where clause.  (I'm guessing it's because SQL doesn't have to scan the table to determine dependencies in other tables.)

    You could make this faster with a little more investigation.  You could remove the dependencies, truncate these tables, then put the dependencies back on.  You might also be able to tell SQL not to write to the transaction log with doing the deletes.

    Note, only do this if you want to drop ALL history and restore data.  I did this because I had so much data.  I'm log shipping multiple DB's on a per minute basis.

  • June 23, 2008 at 12:29 pm

    #832977

    Patrick Smith (7/14/2005)

    As Fred Williams suugests, this speeds it up:

    CREATE

    INDEX [media_set_id] ON [dbo].[backupset] ([media_set_id])

    CREATE

    INDEX [restore_history_id] ON [dbo].[restorefile] ([restore_history_id])

    CREATE

    INDEX [restore_history_id] ON [dbo].[restorefilegroup] ([restore_history_id])

    That really makes a huge difference. Even with only 20 000 records in those tables, it was going to take forever. With the indexes, it went from 20K to nothing in a few seconds.

  • September 10, 2008 at 7:05 am

    #869590

    Brilliant! Without indexes, 200K rows in 7 hours. With indexes, 2.6million rows in 35 minutes!!! :w00t:

    First create the indexes as advised (allow around 10 minutes to complete):

    CREATE

    INDEX [media_set_id] ON [dbo].[backupset] ([media_set_id])

    CREATE

    INDEX [restore_history_id] ON [dbo].[restorefile] ([restore_history_id])

    CREATE

    INDEX [restore_history_id] ON [dbo].[restorefilegroup] ([restore_history_id])

    Then run the original command:

    EXEC sp_delete_backuphistory 'your date'

    Then while the delete is running, run these against MSDB at the same time to watch the progress: 😉

    select count (*) from restorefile

    select count (*) from restorefilegroup

    select count (*) from restorehistory

    select count (*) from backupfile

    select count (*) from backupset

    select count (*) from backupmediafamily

    select count (*) from backupmediaset

    Thanks to all for the info, this really helped!

  • December 17, 2008 at 5:42 am

    #913736

    Amusingly, I spent over an hour on this today and found the same solution (creating an index on backupset.media_set_id). Only after sorting it out on all our servers did I think to make a note on SQLServerCentral.com and find that everyone knows about this already! 🙂

    It's very scary (to me) that the two restore tables don't have ANY index or primary key on them - but in my case it doesn't matter because the tables are practically empty anyway.

    I assume this stuff is fixed in 2005/2008?

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • December 17, 2008 at 6:00 am

    #913748

    I don't think so, I had to create indexed on my 2005 instance (now that I think of it I didn't check if the indexing changed in yukon).

    Anyone care to analyse this, I got reports due this morning :hehe:.

  • December 17, 2008 at 2:15 pm

    #914159

    No change in 2005 too.

    Mj

  • January 15, 2015 at 1:47 am

    #1770345

    Yep....

    Creating the 3 indexes worked for me too

    This is why i love SqlServerCentral! 🙂

  • April 6, 2015 at 12:36 pm

    #1789021

    Has anyone noticed any backup through-put increase while taking backup's since implementing these indexes?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply