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
Showing posts with label history. Show all posts
Showing posts with label history. Show all posts
Wednesday, March 21, 2012
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...
>
|||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...[vbcol=seagreen]
> 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:
|||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...
>
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...
>
|||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...[vbcol=seagreen]
> 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:
|||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...
>
deleting backup history in order to allow for restore
Hello:
Is there an automated way of having SQL delete backup jobs? I ran
sp_delete_backuphistory against the msbd database in SQL Server 2000
8.00.2039 and I had to stop it because it was taking forever to run.
I'm surprised that SQL does not automatically delete backup history since
maintenance plans for automatically deleting backups. (Any relief in SQL
2005?)
The major reason that I ask about this is because when we conduct a manual
restore of a database, we cannot do so by right-clicking on that database in
Enterprise Manager. Enterprise Manager freezes when we choose All
Tasks...Restore. So, we end up having to instead run a script in Query
Analyzer to restore the database.
Someone on this message board told me to delete backup history. But, again,
that takes too long. And, when I tried to do a restore in Enterprise Manager
just now, I got the same result (though I did cancel the process).
Any ideas?
Thanks!
childofthe1980s
The first time you delete the history it will take a long time if it has
never been done before. Just let it run until it finishes otherwise you roll
it back and you are no better off than before. But once you clear out the
garbage that was in there it will only take a second or less to delete about
a weeks worth if you do it on a regular basis. So set up a scheduled job
that calls this once a week and pass in a datetime that leaves you the week
or two that you want to keep for history.
DECLARE @.Date DATETIME
SET @.Date = DATEADD(wk,-1,GETDATE())
EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
In 2005 they have added a task for the MP to dod this but it does absolutely
nothing more than what I show above and still needs to be done regulary. The
problem is there is data in 3 tables and they are not properly indexed so a
delete on lots of data takes a long time.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
> Hello:
> Is there an automated way of having SQL delete backup jobs? I ran
> sp_delete_backuphistory against the msbd database in SQL Server 2000
> 8.00.2039 and I had to stop it because it was taking forever to run.
> I'm surprised that SQL does not automatically delete backup history since
> maintenance plans for automatically deleting backups. (Any relief in SQL
> 2005?)
> The major reason that I ask about this is because when we conduct a manual
> restore of a database, we cannot do so by right-clicking on that database
> in
> Enterprise Manager. Enterprise Manager freezes when we choose All
> Tasks...Restore. So, we end up having to instead run a script in Query
> Analyzer to restore the database.
> Someone on this message board told me to delete backup history. But,
> again,
> that takes too long. And, when I tried to do a restore in Enterprise
> Manager
> just now, I got the same result (though I did cancel the process).
> Any ideas?
> Thanks!
> childofthe1980s
>
>
>
|||Thanks, Andrew!
childofthe1980s
"Andrew J. Kelly" wrote:
> The first time you delete the history it will take a long time if it has
> never been done before. Just let it run until it finishes otherwise you roll
> it back and you are no better off than before. But once you clear out the
> garbage that was in there it will only take a second or less to delete about
> a weeks worth if you do it on a regular basis. So set up a scheduled job
> that calls this once a week and pass in a datetime that leaves you the week
> or two that you want to keep for history.
> DECLARE @.Date DATETIME
> SET @.Date = DATEADD(wk,-1,GETDATE())
> EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
>
> In 2005 they have added a task for the MP to dod this but it does absolutely
> nothing more than what I show above and still needs to be done regulary. The
> problem is there is data in 3 tables and they are not properly indexed so a
> delete on lots of data takes a long time.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
>
Is there an automated way of having SQL delete backup jobs? I ran
sp_delete_backuphistory against the msbd database in SQL Server 2000
8.00.2039 and I had to stop it because it was taking forever to run.
I'm surprised that SQL does not automatically delete backup history since
maintenance plans for automatically deleting backups. (Any relief in SQL
2005?)
The major reason that I ask about this is because when we conduct a manual
restore of a database, we cannot do so by right-clicking on that database in
Enterprise Manager. Enterprise Manager freezes when we choose All
Tasks...Restore. So, we end up having to instead run a script in Query
Analyzer to restore the database.
Someone on this message board told me to delete backup history. But, again,
that takes too long. And, when I tried to do a restore in Enterprise Manager
just now, I got the same result (though I did cancel the process).
Any ideas?
Thanks!
childofthe1980s
The first time you delete the history it will take a long time if it has
never been done before. Just let it run until it finishes otherwise you roll
it back and you are no better off than before. But once you clear out the
garbage that was in there it will only take a second or less to delete about
a weeks worth if you do it on a regular basis. So set up a scheduled job
that calls this once a week and pass in a datetime that leaves you the week
or two that you want to keep for history.
DECLARE @.Date DATETIME
SET @.Date = DATEADD(wk,-1,GETDATE())
EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
In 2005 they have added a task for the MP to dod this but it does absolutely
nothing more than what I show above and still needs to be done regulary. The
problem is there is data in 3 tables and they are not properly indexed so a
delete on lots of data takes a long time.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
> Hello:
> Is there an automated way of having SQL delete backup jobs? I ran
> sp_delete_backuphistory against the msbd database in SQL Server 2000
> 8.00.2039 and I had to stop it because it was taking forever to run.
> I'm surprised that SQL does not automatically delete backup history since
> maintenance plans for automatically deleting backups. (Any relief in SQL
> 2005?)
> The major reason that I ask about this is because when we conduct a manual
> restore of a database, we cannot do so by right-clicking on that database
> in
> Enterprise Manager. Enterprise Manager freezes when we choose All
> Tasks...Restore. So, we end up having to instead run a script in Query
> Analyzer to restore the database.
> Someone on this message board told me to delete backup history. But,
> again,
> that takes too long. And, when I tried to do a restore in Enterprise
> Manager
> just now, I got the same result (though I did cancel the process).
> Any ideas?
> Thanks!
> childofthe1980s
>
>
>
|||Thanks, Andrew!
childofthe1980s
"Andrew J. Kelly" wrote:
> The first time you delete the history it will take a long time if it has
> never been done before. Just let it run until it finishes otherwise you roll
> it back and you are no better off than before. But once you clear out the
> garbage that was in there it will only take a second or less to delete about
> a weeks worth if you do it on a regular basis. So set up a scheduled job
> that calls this once a week and pass in a datetime that leaves you the week
> or two that you want to keep for history.
> DECLARE @.Date DATETIME
> SET @.Date = DATEADD(wk,-1,GETDATE())
> EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
>
> In 2005 they have added a task for the MP to dod this but it does absolutely
> nothing more than what I show above and still needs to be done regulary. The
> problem is there is data in 3 tables and they are not properly indexed so a
> delete on lots of data takes a long time.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
>
deleting backup history in order to allow for restore
Hello:
Is there an automated way of having SQL delete backup jobs? I ran
sp_delete_backuphistory against the msbd database in SQL Server 2000
8.00.2039 and I had to stop it because it was taking forever to run.
I'm surprised that SQL does not automatically delete backup history since
maintenance plans for automatically deleting backups. (Any relief in SQL
2005?)
The major reason that I ask about this is because when we conduct a manual
restore of a database, we cannot do so by right-clicking on that database in
Enterprise Manager. Enterprise Manager freezes when we choose All
Tasks...Restore. So, we end up having to instead run a script in Query
Analyzer to restore the database.
Someone on this message board told me to delete backup history. But, again,
that takes too long. And, when I tried to do a restore in Enterprise Manager
just now, I got the same result (though I did cancel the process).
Any ideas?
Thanks!
childofthe1980sThe first time you delete the history it will take a long time if it has
never been done before. Just let it run until it finishes otherwise you roll
it back and you are no better off than before. But once you clear out the
garbage that was in there it will only take a second or less to delete about
a weeks worth if you do it on a regular basis. So set up a scheduled job
that calls this once a week and pass in a datetime that leaves you the week
or two that you want to keep for history.
DECLARE @.Date DATETIME
SET @.Date = DATEADD(wk,-1,GETDATE())
EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
In 2005 they have added a task for the MP to dod this but it does absolutely
nothing more than what I show above and still needs to be done regulary. The
problem is there is data in 3 tables and they are not properly indexed so a
delete on lots of data takes a long time.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
> Hello:
> Is there an automated way of having SQL delete backup jobs? I ran
> sp_delete_backuphistory against the msbd database in SQL Server 2000
> 8.00.2039 and I had to stop it because it was taking forever to run.
> I'm surprised that SQL does not automatically delete backup history since
> maintenance plans for automatically deleting backups. (Any relief in SQL
> 2005?)
> The major reason that I ask about this is because when we conduct a manual
> restore of a database, we cannot do so by right-clicking on that database
> in
> Enterprise Manager. Enterprise Manager freezes when we choose All
> Tasks...Restore. So, we end up having to instead run a script in Query
> Analyzer to restore the database.
> Someone on this message board told me to delete backup history. But,
> again,
> that takes too long. And, when I tried to do a restore in Enterprise
> Manager
> just now, I got the same result (though I did cancel the process).
> Any ideas?
> Thanks!
> childofthe1980s
>
>
>|||Thanks, Andrew!
childofthe1980s
"Andrew J. Kelly" wrote:
> The first time you delete the history it will take a long time if it has
> never been done before. Just let it run until it finishes otherwise you roll
> it back and you are no better off than before. But once you clear out the
> garbage that was in there it will only take a second or less to delete about
> a weeks worth if you do it on a regular basis. So set up a scheduled job
> that calls this once a week and pass in a datetime that leaves you the week
> or two that you want to keep for history.
> DECLARE @.Date DATETIME
> SET @.Date = DATEADD(wk,-1,GETDATE())
> EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
>
> In 2005 they have added a task for the MP to dod this but it does absolutely
> nothing more than what I show above and still needs to be done regulary. The
> problem is there is data in 3 tables and they are not properly indexed so a
> delete on lots of data takes a long time.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
> > Hello:
> >
> > Is there an automated way of having SQL delete backup jobs? I ran
> > sp_delete_backuphistory against the msbd database in SQL Server 2000
> > 8.00.2039 and I had to stop it because it was taking forever to run.
> >
> > I'm surprised that SQL does not automatically delete backup history since
> > maintenance plans for automatically deleting backups. (Any relief in SQL
> > 2005?)
> >
> > The major reason that I ask about this is because when we conduct a manual
> > restore of a database, we cannot do so by right-clicking on that database
> > in
> > Enterprise Manager. Enterprise Manager freezes when we choose All
> > Tasks...Restore. So, we end up having to instead run a script in Query
> > Analyzer to restore the database.
> >
> > Someone on this message board told me to delete backup history. But,
> > again,
> > that takes too long. And, when I tried to do a restore in Enterprise
> > Manager
> > just now, I got the same result (though I did cancel the process).
> >
> > Any ideas?
> >
> > Thanks!
> >
> > childofthe1980s
> >
> >
> >
> >
> >
>sql
Is there an automated way of having SQL delete backup jobs? I ran
sp_delete_backuphistory against the msbd database in SQL Server 2000
8.00.2039 and I had to stop it because it was taking forever to run.
I'm surprised that SQL does not automatically delete backup history since
maintenance plans for automatically deleting backups. (Any relief in SQL
2005?)
The major reason that I ask about this is because when we conduct a manual
restore of a database, we cannot do so by right-clicking on that database in
Enterprise Manager. Enterprise Manager freezes when we choose All
Tasks...Restore. So, we end up having to instead run a script in Query
Analyzer to restore the database.
Someone on this message board told me to delete backup history. But, again,
that takes too long. And, when I tried to do a restore in Enterprise Manager
just now, I got the same result (though I did cancel the process).
Any ideas?
Thanks!
childofthe1980sThe first time you delete the history it will take a long time if it has
never been done before. Just let it run until it finishes otherwise you roll
it back and you are no better off than before. But once you clear out the
garbage that was in there it will only take a second or less to delete about
a weeks worth if you do it on a regular basis. So set up a scheduled job
that calls this once a week and pass in a datetime that leaves you the week
or two that you want to keep for history.
DECLARE @.Date DATETIME
SET @.Date = DATEADD(wk,-1,GETDATE())
EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
In 2005 they have added a task for the MP to dod this but it does absolutely
nothing more than what I show above and still needs to be done regulary. The
problem is there is data in 3 tables and they are not properly indexed so a
delete on lots of data takes a long time.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
> Hello:
> Is there an automated way of having SQL delete backup jobs? I ran
> sp_delete_backuphistory against the msbd database in SQL Server 2000
> 8.00.2039 and I had to stop it because it was taking forever to run.
> I'm surprised that SQL does not automatically delete backup history since
> maintenance plans for automatically deleting backups. (Any relief in SQL
> 2005?)
> The major reason that I ask about this is because when we conduct a manual
> restore of a database, we cannot do so by right-clicking on that database
> in
> Enterprise Manager. Enterprise Manager freezes when we choose All
> Tasks...Restore. So, we end up having to instead run a script in Query
> Analyzer to restore the database.
> Someone on this message board told me to delete backup history. But,
> again,
> that takes too long. And, when I tried to do a restore in Enterprise
> Manager
> just now, I got the same result (though I did cancel the process).
> Any ideas?
> Thanks!
> childofthe1980s
>
>
>|||Thanks, Andrew!
childofthe1980s
"Andrew J. Kelly" wrote:
> The first time you delete the history it will take a long time if it has
> never been done before. Just let it run until it finishes otherwise you roll
> it back and you are no better off than before. But once you clear out the
> garbage that was in there it will only take a second or less to delete about
> a weeks worth if you do it on a regular basis. So set up a scheduled job
> that calls this once a week and pass in a datetime that leaves you the week
> or two that you want to keep for history.
> DECLARE @.Date DATETIME
> SET @.Date = DATEADD(wk,-1,GETDATE())
> EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
>
> In 2005 they have added a task for the MP to dod this but it does absolutely
> nothing more than what I show above and still needs to be done regulary. The
> problem is there is data in 3 tables and they are not properly indexed so a
> delete on lots of data takes a long time.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
> > Hello:
> >
> > Is there an automated way of having SQL delete backup jobs? I ran
> > sp_delete_backuphistory against the msbd database in SQL Server 2000
> > 8.00.2039 and I had to stop it because it was taking forever to run.
> >
> > I'm surprised that SQL does not automatically delete backup history since
> > maintenance plans for automatically deleting backups. (Any relief in SQL
> > 2005?)
> >
> > The major reason that I ask about this is because when we conduct a manual
> > restore of a database, we cannot do so by right-clicking on that database
> > in
> > Enterprise Manager. Enterprise Manager freezes when we choose All
> > Tasks...Restore. So, we end up having to instead run a script in Query
> > Analyzer to restore the database.
> >
> > Someone on this message board told me to delete backup history. But,
> > again,
> > that takes too long. And, when I tried to do a restore in Enterprise
> > Manager
> > just now, I got the same result (though I did cancel the process).
> >
> > Any ideas?
> >
> > Thanks!
> >
> > childofthe1980s
> >
> >
> >
> >
> >
>sql
Sunday, March 11, 2012
DeletedFlag field and unique keys
I was thinking of using a deleted flag rather then deleting a record so that history (auditing, etc.) could be maintained. In certain tables I would like to preserve a unique key on a field (say name or code) of all non-deleted records. Is there any way to do this. I cannot have a Code + DeletedFlag field because there may be multiple records that have been deleted with the same code. I also don't want to include the deleted "codes" in the unique key because I want the user to be able to see the codes they can't use without viewing the deleted records.May be Code+DeletedFlag+DeletedDateTime could be good idea|||That means there could be duplicate codes for active entries which is what I am trying to avoid.|||You could always keep a separate table for the deleted items (with _deleted at the end of the name, or a similar convention). I do this now and then. The deleted items table wouldn't have the uniqueness constraints on it, and you could still use a view to look at the combined tables. Then slap an AFTER DELETE trigger on the original table to automatically move rows into the deleted items table.
|||
|||
A 'better' way to handle archiving is to have separate archiving tables. Same schema, no IDENTITY fields, no constraints, index only on PK, a couple of additional columns:
ChangeBy varchar(50)DEFAULT system_user ChangeDate datetime DEFAULT getdate()|||I have thought about another table (deleted or archived) for records and that probably is the most sensible way to deal with this issue. Ideally I wanted to create an audit table and for any table I wanted to audit I would have a join table (transaction_audit for example). The audit record would hold time, user, action (cud), ip, etc. This way when looking at any record from an audited table you could see a list of actions that were performed on it. Maybe not the complete history (every changed value) which might be overkill but who did what (in general) to it when and from where. Deleted records would be kept in the database in their last state so that activity would be shown as activity and not masked.|||I agree with Arnie.I do this all the time. Create a table exactly like the original with no identity or constraints with a few extra fields on the end, like ChangeType, UserID, ChangeDateTime, called tablename_audit. Then setup a trigger to "insert into table_audit select *, "D", @.userid, getdate() from deleted" the audit table.
The only thing you need to remember with this is, when you change the original, you MUST also change the fields in the audit log to match. This can take some time, if you have many audit records.
DeletedFlag field and unique keys
I was thinking of using a deleted flag rather then deleting a record so that history (auditing, etc.) could be maintained. In certain tables I would like to preserve a unique key on a field (say name or code) of all non-deleted records. Is there any way to do this. I cannot have a Code + DeletedFlag field because there may be multiple records that have been deleted with the same code. I also don't want to include the deleted "codes" in the unique key because I want the user to be able to see the codes they can't use without viewing the deleted records.May be Code+DeletedFlag+DeletedDateTime could be good idea|||That means there could be duplicate codes for active entries which is what I am trying to avoid.|||You could always keep a separate table for the deleted items (with _deleted at the end of the name, or a similar convention). I do this now and then. The deleted items table wouldn't have the uniqueness constraints on it, and you could still use a view to look at the combined tables. Then slap an AFTER DELETE trigger on the original table to automatically move rows into the deleted items table.
|||
|||
A 'better' way to handle archiving is to have separate archiving tables. Same schema, no IDENTITY fields, no constraints, index only on PK, a couple of additional columns:
ChangeBy varchar(50)DEFAULT system_user ChangeDate datetime DEFAULT getdate()|||I have thought about another table (deleted or archived) for records and that probably is the most sensible way to deal with this issue. Ideally I wanted to create an audit table and for any table I wanted to audit I would have a join table (transaction_audit for example). The audit record would hold time, user, action (cud), ip, etc. This way when looking at any record from an audited table you could see a list of actions that were performed on it. Maybe not the complete history (every changed value) which might be overkill but who did what (in general) to it when and from where. Deleted records would be kept in the database in their last state so that activity would be shown as activity and not masked.|||I agree with Arnie.I do this all the time. Create a table exactly like the original with no identity or constraints with a few extra fields on the end, like ChangeType, UserID, ChangeDateTime, called tablename_audit. Then setup a trigger to "insert into table_audit select *, "D", @.userid, getdate() from deleted" the audit table.
The only thing you need to remember with this is, when you change the original, you MUST also change the fields in the audit log to match. This can take some time, if you have many audit records.
Subscribe to:
Posts (Atom)