deleting database backup history

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AkAlan - 21 Mar 2008 17:15 GMT

I started running sp_delete_backuphistory before leaving work yesterday and
now 17 hours later it is still running. I know there where over 270,000
records in the backupset table so I knew it would take a while to run but
this seems too long. Should I cancel the query execution and do it in smaller
chunks, or let it go now that it's been running this long.

I posted this same post earlier today but it looked like it didn't post so,
my appologies if this is a duplicate.

Reply to this Message

Geoff N. Hiten - 21 Mar 2008 18:13 GMT

This might help:
http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx

Signature

Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP

>I started running sp_delete_backuphistory before leaving work yesterday and
> now 17 hours later it is still running. I know there where over 270,000
[quoted text clipped - 6 lines]
> so,
> my appologies if this is a duplicate.

Reply to this Message

AkAlan - 21 Mar 2008 18:42 GMT

Geoff, Thanks very much. I was looking at this link yesterday but didn't do
the indexing first. I have stopped the query now and the backupset table is
down to 167000 records from over 270000. Next question is approximately how
long will it take to run the indexing script? I'm thinking I would like to
run the script while I'm at work then run the sp_delete_backuphistory over
the weekend. Thanks again, this problem has been a pain for quite a while.

> This might help:
> http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx
[quoted text clipped - 9 lines]
> > so,
> > my appologies if this is a duplicate.

Reply to this Message

Geoff N. Hiten - 21 Mar 2008 19:16 GMT

Unless you are on a very slow system, indexing should be just a few minutes,
but it will block backups during that time. And once you create the
indexes, the delete will go VERY fast.

Signature

Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP

> Geoff, Thanks very much. I was looking at this link yesterday but didn't
> do
[quoted text clipped - 21 lines]
>> > so,
>> > my appologies if this is a duplicate.

Reply to this Message

AkAlan - 21 Mar 2008 19:30 GMT

THanks again Geoff! The indexing only took a minute and now I'm using the
nibbler code from a responce the your post in the link and it's taking about
2 minutes to purge 10 days worth.

> Unless you are on a very slow system, indexing should be just a few minutes,
> but it will block backups during that time. And once you create the
[quoted text clipped - 25 lines]
> >> > so,
> >> > my appologies if this is a duplicate.

Reply to this Message