deleting database backup history

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.This might help:
http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:DA5E5F91-00DB-4C2B-8041-92C2D86D07CF@.microsoft.com...
>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.|||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.
"Geoff N. Hiten" wrote:
> This might help:
> http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:DA5E5F91-00DB-4C2B-8041-92C2D86D07CF@.microsoft.com...
> >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.
>|||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.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:56E5BD88-7B1A-4ABF-A957-C25D780FB01E@.microsoft.com...
> 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.
> "Geoff N. Hiten" wrote:
>> This might help:
>> http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
>> news:DA5E5F91-00DB-4C2B-8041-92C2D86D07CF@.microsoft.com...
>> >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.
>>|||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.
"Geoff N. Hiten" wrote:
> 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.
>
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:56E5BD88-7B1A-4ABF-A957-C25D780FB01E@.microsoft.com...
> > 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.
> >
> > "Geoff N. Hiten" wrote:
> >
> >> This might help:
> >> http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx
> >>
> >> --
> >> Geoff N. Hiten
> >> Senior SQL Infrastructure Consultant
> >> Microsoft SQL Server MVP
> >>
> >>
> >> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> >> news:DA5E5F91-00DB-4C2B-8041-92C2D86D07CF@.microsoft.com...
> >> >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.
> >>
> >>
>sql