Alternative to sp_delete_Backuphistory

I have a SQL 2000 server that has 300 databases. There is a requirement tha
t
these databases have their logs backed up every 30 minutes. This creates a
huge number of entries in the backup/restore tables in the msdb database.
I know I can delete backup history with the sp_delete_backuphistory command.
However, this command takes forever. Even deleting 30 minutes worth of
backups takes 15 or more minutes. I find I am creating entries in the
backup/restore tables faster than I could possible delete them.
Is there an alteranate way to clear out the backup/restore tables? Could is
simply truncate the tables?
Thanks for any info.
LehrSJYes, I ran into the same problem when I discovered the backup tables in
msdb were HUGE... instead of running the sp, I just truncated. The only
thing you lose by doing this is the ability to use Enterprise Manager
of Mgmt Studio to do your restore, as it relies on the information in
those tables when suggesting which backups to apply. If you can afford
to lose this, no big deal. The information will just get repopulated on
your next backups.
Also, once the tables are empty, set up a nightly job to run the
sp_delete_backuphistory, keep as many days worth of history as you
specify.
LehrSJ wrote:
> I have a SQL 2000 server that has 300 databases. There is a requirement t
hat
> these databases have their logs backed up every 30 minutes. This creates
a
> huge number of entries in the backup/restore tables in the msdb database.
> I know I can delete backup history with the sp_delete_backuphistory comman
d.
> However, this command takes forever. Even deleting 30 minutes worth of
> backups takes 15 or more minutes. I find I am creating entries in the
> backup/restore tables faster than I could possible delete them.
> Is there an alteranate way to clear out the backup/restore tables? Could
is
> simply truncate the tables?
> Thanks for any info.
> --
> LehrSJ|||Yes, you can truncate the tables. See the system table map to see the
relationships btwn the backup tables as there are dependencies between
them. All you lose by truncating the tables is the ability to use EM or
Mgmt Studio to do a restore, as it relies on the information in those
tables when suggesting which backups to apply. If you can afford to
lose it, no big deal. The information just gets repopluated on your
next backup.
Meanwhile, you might want to set up a nightly job to run
sp_delete_backuphistory so the table growth is kept in check.
LehrSJ wrote:
> I have a SQL 2000 server that has 300 databases. There is a requirement t
hat
> these databases have their logs backed up every 30 minutes. This creates
a
> huge number of entries in the backup/restore tables in the msdb database.
> I know I can delete backup history with the sp_delete_backuphistory comman
d.
> However, this command takes forever. Even deleting 30 minutes worth of
> backups takes 15 or more minutes. I find I am creating entries in the
> backup/restore tables faster than I could possible delete them.
> Is there an alteranate way to clear out the backup/restore tables? Could
is
> simply truncate the tables?
> Thanks for any info.
> --
> LehrSJ|||LehrSJ wrote:
> I have a SQL 2000 server that has 300 databases. There is a requirement t
hat
> these databases have their logs backed up every 30 minutes. This creates
a
> huge number of entries in the backup/restore tables in the msdb database.
> I know I can delete backup history with the sp_delete_backuphistory comman
d.
> However, this command takes forever. Even deleting 30 minutes worth of
> backups takes 15 or more minutes. I find I am creating entries in the
> backup/restore tables faster than I could possible delete them.
> Is there an alteranate way to clear out the backup/restore tables? Could
is
> simply truncate the tables?
> Thanks for any info.
>
The problem is that there are no useful indexes on the backup history
tables. Create these indexes, then sp_delete_backuphistory will fly:
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 )
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you for this information. I didn't see why I couldn't truncate but I
was worried that there might be something that I didn't know about. I
understand what I will lose the ability for Enterprise manager to easily do
restores.
--
LehrSJ
"tootsuite@.gmail.com" wrote:

> Yes, you can truncate the tables. See the system table map to see the
> relationships btwn the backup tables as there are dependencies between
> them. All you lose by truncating the tables is the ability to use EM or
> Mgmt Studio to do a restore, as it relies on the information in those
> tables when suggesting which backups to apply. If you can afford to
> lose it, no big deal. The information just gets repopluated on your
> next backup.
> Meanwhile, you might want to set up a nightly job to run
> sp_delete_backuphistory so the table growth is kept in check.
> LehrSJ wrote:
>

Alternative to sp_delete_Backuphistory

I have a SQL 2000 server that has 300 databases. There is a requirement that
these databases have their logs backed up every 30 minutes. This creates a
huge number of entries in the backup/restore tables in the msdb database.
I know I can delete backup history with the sp_delete_backuphistory command.
However, this command takes forever. Even deleting 30 minutes worth of
backups takes 15 or more minutes. I find I am creating entries in the
backup/restore tables faster than I could possible delete them.
Is there an alteranate way to clear out the backup/restore tables? Could is
simply truncate the tables?
Thanks for any info.
LehrSJ
Yes, I ran into the same problem when I discovered the backup tables in
msdb were HUGE... instead of running the sp, I just truncated. The only
thing you lose by doing this is the ability to use Enterprise Manager
of Mgmt Studio to do your restore, as it relies on the information in
those tables when suggesting which backups to apply. If you can afford
to lose this, no big deal. The information will just get repopulated on
your next backups.
Also, once the tables are empty, set up a nightly job to run the
sp_delete_backuphistory, keep as many days worth of history as you
specify.
LehrSJ wrote:
> I have a SQL 2000 server that has 300 databases. There is a requirement that
> these databases have their logs backed up every 30 minutes. This creates a
> huge number of entries in the backup/restore tables in the msdb database.
> I know I can delete backup history with the sp_delete_backuphistory command.
> However, this command takes forever. Even deleting 30 minutes worth of
> backups takes 15 or more minutes. I find I am creating entries in the
> backup/restore tables faster than I could possible delete them.
> Is there an alteranate way to clear out the backup/restore tables? Could is
> simply truncate the tables?
> Thanks for any info.
> --
> LehrSJ
|||Yes, you can truncate the tables. See the system table map to see the
relationships btwn the backup tables as there are dependencies between
them. All you lose by truncating the tables is the ability to use EM or
Mgmt Studio to do a restore, as it relies on the information in those
tables when suggesting which backups to apply. If you can afford to
lose it, no big deal. The information just gets repopluated on your
next backup.
Meanwhile, you might want to set up a nightly job to run
sp_delete_backuphistory so the table growth is kept in check.
LehrSJ wrote:
> I have a SQL 2000 server that has 300 databases. There is a requirement that
> these databases have their logs backed up every 30 minutes. This creates a
> huge number of entries in the backup/restore tables in the msdb database.
> I know I can delete backup history with the sp_delete_backuphistory command.
> However, this command takes forever. Even deleting 30 minutes worth of
> backups takes 15 or more minutes. I find I am creating entries in the
> backup/restore tables faster than I could possible delete them.
> Is there an alteranate way to clear out the backup/restore tables? Could is
> simply truncate the tables?
> Thanks for any info.
> --
> LehrSJ
|||LehrSJ wrote:
> I have a SQL 2000 server that has 300 databases. There is a requirement that
> these databases have their logs backed up every 30 minutes. This creates a
> huge number of entries in the backup/restore tables in the msdb database.
> I know I can delete backup history with the sp_delete_backuphistory command.
> However, this command takes forever. Even deleting 30 minutes worth of
> backups takes 15 or more minutes. I find I am creating entries in the
> backup/restore tables faster than I could possible delete them.
> Is there an alteranate way to clear out the backup/restore tables? Could is
> simply truncate the tables?
> Thanks for any info.
>
The problem is that there are no useful indexes on the backup history
tables. Create these indexes, then sp_delete_backuphistory will fly:
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 )
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thank you for this information. I didn't see why I couldn't truncate but I
was worried that there might be something that I didn't know about. I
understand what I will lose the ability for Enterprise manager to easily do
restores.
LehrSJ
"tootsuite@.gmail.com" wrote:

> Yes, you can truncate the tables. See the system table map to see the
> relationships btwn the backup tables as there are dependencies between
> them. All you lose by truncating the tables is the ability to use EM or
> Mgmt Studio to do a restore, as it relies on the information in those
> tables when suggesting which backups to apply. If you can afford to
> lose it, no big deal. The information just gets repopluated on your
> next backup.
> Meanwhile, you might want to set up a nightly job to run
> sp_delete_backuphistory so the table growth is kept in check.
> LehrSJ wrote:
>