MSDB question

WHat would be the danger of adding a couple of indexes to the msdb system
tables (particularly the backup/restore jobs tables)? I know that a hotfix
or sp update may drop the tables, but if we are staying on sp3 or sp4 for
now, would it cause a sytem wide issue?
This is for SQL2000 on a Win2003 server.
Thankscw
Why? MS strongly recommended not direct updating system tables.
"cw" <cw@.3mc.com> wrote in message
news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
> WHat would be the danger of adding a couple of indexes to the msdb system
> tables (particularly the backup/restore jobs tables)? I know that a
> hotfix or sp update may drop the tables, but if we are staying on sp3 or
> sp4 for now, would it cause a sytem wide issue?
> This is for SQL2000 on a Win2003 server.
> Thanks
>|||I do it all the time. One side effect of constant build testing is that you
don't run something for a long time and see some of the life cycle issues
that we see in real life. Just don't add keys or constraints, only indexes
and you should be fine.
Here is the set I use:
/************************************************************************
* *
* Title: msdb index creation *
* Author: Geoff N. Hiten *
* Purpose: Index msdb database *
* Date: 12/12/2005 *
* Modifications: *
* *
* 06-22-2001 *
* Sample Entry *
* *
* *
************************************************************************/
use
msdb
go
--backupset
Create
index IX_backupset_backup_set_id on backupset (backup_set_id)
go
Create
index IX_backupset_backup_set_uuid on backupset (backup_set_uuid)
go
Create
index IX_backupset_media_set_id on backupset (media_set_id)
go
Create
index IX_backupset_backup_finish_date on backupset (backup_finish_date)
go
Create
index IX_backupset_backup_start_date on backupset (backup_start_date)
go
--backupmediaset
Create
index IX_backupmediaset_media_set_id on backupmediaset (media_set_id)
go
--backupfile
Create
index IX_backupfile_backup_set_id on backupfile (backup_set_id)
go
--backupmediafamily
Create
index IX_backupmediafamily_media_set_id on backupmediafamily (media_set_id)
go
--restorehistory
Create
index IX_restorehistory_restore_history_id on restorehistory
(restore_history_id)
go
Create
index IX_restorehistory_backup_set_id on restorehistory (backup_set_id)
go
--restorefile
Create
index IX_restorefile_restore_history_id on restorefile (restore_history_id)
go
--restorefilegroup
Create
index IX_restorefilegroup_restore_history_id on restorefilegroup
(restore_history_id)
go
"cw" <cw@.3mc.com> wrote in message
news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
> WHat would be the danger of adding a couple of indexes to the msdb system
> tables (particularly the backup/restore jobs tables)? I know that a
> hotfix or sp update may drop the tables, but if we are staying on sp3 or
> sp4 for now, would it cause a sytem wide issue?
> This is for SQL2000 on a Win2003 server.
> Thanks
>|||Thanks - several of these indexes you have listed below are the same ones I
added. I had dropped an old database from a SQL2000 server (using EM). The
DB had daily backups and several transaction log backups during the day.
This has been going on for the last 2-3 years and the number of records in
the msdb backup system tables for historical purposes was huge. When EM
drops a DB and begins to delete the historical information about the
backups, it called the sp_delete_backup_history procedure. If there was
ever a procedure that needed indexes, this is it. The procedure had been
running all night and a trace showed it doing several table scans using
"lots" of cursors.
After I added the indexes the job finished w/i 10 minutes. We dropped
another database that would have had the same backup history and the
procedure finished w/i 15 minutes.
Thanks for the confirmation -- cw
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%235GjJwqAHHA.996@.TK2MSFTNGP02.phx.gbl...
>I do it all the time. One side effect of constant build testing is that
>you don't run something for a long time and see some of the life cycle
>issues that we see in real life. Just don't add keys or constraints, only
>indexes and you should be fine.
> Here is the set I use:
> /************************************************************************
> * *
> * Title: msdb index creation *
> * Author: Geoff N. Hiten *
> * Purpose: Index msdb database *
> * Date: 12/12/2005 *
> * Modifications: *
> * *
> * 06-22-2001 *
> * Sample Entry *
> * *
> * *
> ************************************************************************/
> use
> msdb
> go
> --backupset
> Create
> index IX_backupset_backup_set_id on backupset (backup_set_id)
> go
> Create
> index IX_backupset_backup_set_uuid on backupset (backup_set_uuid)
> go
> Create
> index IX_backupset_media_set_id on backupset (media_set_id)
> go
> Create
> index IX_backupset_backup_finish_date on backupset (backup_finish_date)
> go
> Create
> index IX_backupset_backup_start_date on backupset (backup_start_date)
> go
> --backupmediaset
> Create
> index IX_backupmediaset_media_set_id on backupmediaset (media_set_id)
> go
> --backupfile
> Create
> index IX_backupfile_backup_set_id on backupfile (backup_set_id)
> go
> --backupmediafamily
> Create
> index IX_backupmediafamily_media_set_id on backupmediafamily
> (media_set_id)
> go
> --restorehistory
> Create
> index IX_restorehistory_restore_history_id on restorehistory
> (restore_history_id)
> go
> Create
> index IX_restorehistory_backup_set_id on restorehistory (backup_set_id)
> go
> --restorefile
> Create
> index IX_restorefile_restore_history_id on restorefile
> (restore_history_id)
> go
> --restorefilegroup
> Create
> index IX_restorefilegroup_restore_history_id on restorefilegroup
> (restore_history_id)
> go
>
> "cw" <cw@.3mc.com> wrote in message
> news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
>> WHat would be the danger of adding a couple of indexes to the msdb system
>> tables (particularly the backup/restore jobs tables)? I know that a
>> hotfix or sp update may drop the tables, but if we are staying on sp3 or
>> sp4 for now, would it cause a sytem wide issue?
>> This is for SQL2000 on a Win2003 server.
>> Thanks
>|||I also use a "nibbler" to drive the s_delete_backup_history cleanuot proc.
It deletes one day at a time until it gets to the minimum retention time I
set. And no, I don't think I will post that.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"cw" <cw@.3mc.com> wrote in message
news:OHXUGKrAHHA.3540@.TK2MSFTNGP03.phx.gbl...
> Thanks - several of these indexes you have listed below are the same ones
> I added. I had dropped an old database from a SQL2000 server (using EM).
> The DB had daily backups and several transaction log backups during the
> day. This has been going on for the last 2-3 years and the number of
> records in the msdb backup system tables for historical purposes was huge.
> When EM drops a DB and begins to delete the historical information about
> the backups, it called the sp_delete_backup_history procedure. If there
> was ever a procedure that needed indexes, this is it. The procedure had
> been running all night and a trace showed it doing several table scans
> using "lots" of cursors.
> After I added the indexes the job finished w/i 10 minutes. We dropped
> another database that would have had the same backup history and the
> procedure finished w/i 15 minutes.
> Thanks for the confirmation -- cw
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%235GjJwqAHHA.996@.TK2MSFTNGP02.phx.gbl...
>>I do it all the time. One side effect of constant build testing is that
>>you don't run something for a long time and see some of the life cycle
>>issues that we see in real life. Just don't add keys or constraints, only
>>indexes and you should be fine.
>> Here is the set I use:
>> /************************************************************************
>> * *
>> * Title: msdb index creation *
>> * Author: Geoff N. Hiten *
>> * Purpose: Index msdb database *
>> * Date: 12/12/2005 *
>> * Modifications: *
>> * *
>> * 06-22-2001 *
>> * Sample Entry *
>> * *
>> * *
>> ************************************************************************/
>> use
>> msdb
>> go
>> --backupset
>> Create
>> index IX_backupset_backup_set_id on backupset (backup_set_id)
>> go
>> Create
>> index IX_backupset_backup_set_uuid on backupset (backup_set_uuid)
>> go
>> Create
>> index IX_backupset_media_set_id on backupset (media_set_id)
>> go
>> Create
>> index IX_backupset_backup_finish_date on backupset (backup_finish_date)
>> go
>> Create
>> index IX_backupset_backup_start_date on backupset (backup_start_date)
>> go
>> --backupmediaset
>> Create
>> index IX_backupmediaset_media_set_id on backupmediaset (media_set_id)
>> go
>> --backupfile
>> Create
>> index IX_backupfile_backup_set_id on backupfile (backup_set_id)
>> go
>> --backupmediafamily
>> Create
>> index IX_backupmediafamily_media_set_id on backupmediafamily
>> (media_set_id)
>> go
>> --restorehistory
>> Create
>> index IX_restorehistory_restore_history_id on restorehistory
>> (restore_history_id)
>> go
>> Create
>> index IX_restorehistory_backup_set_id on restorehistory (backup_set_id)
>> go
>> --restorefile
>> Create
>> index IX_restorefile_restore_history_id on restorefile
>> (restore_history_id)
>> go
>> --restorefilegroup
>> Create
>> index IX_restorefilegroup_restore_history_id on restorefilegroup
>> (restore_history_id)
>> go
>>
>> "cw" <cw@.3mc.com> wrote in message
>> news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
>> WHat would be the danger of adding a couple of indexes to the msdb
>> system tables (particularly the backup/restore jobs tables)? I know
>> that a hotfix or sp update may drop the tables, but if we are staying on
>> sp3 or sp4 for now, would it cause a sytem wide issue?
>> This is for SQL2000 on a Win2003 server.
>> Thanks
>>
>|||Several months ago I noticed that these backup tables in msdb were
growing out of control. Attempts to delete rows using the delete backup
history sp took *forever*. Since it is my understanding that these
tables are really only needed if you use the interfaces, such as EM or
Mgmt Studio, to do restores, I decided to just truncate the tables then
start running the sp every night to only keep 3 days history. This
method was much faster needless to say and nothing was the worse for
it.
Geoff N. Hiten wrote:
> I also use a "nibbler" to drive the s_delete_backup_history cleanuot proc.
> It deletes one day at a time until it gets to the minimum retention time I
> set. And no, I don't think I will post that.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "cw" <cw@.3mc.com> wrote in message
> news:OHXUGKrAHHA.3540@.TK2MSFTNGP03.phx.gbl...
> > Thanks - several of these indexes you have listed below are the same ones
> > I added. I had dropped an old database from a SQL2000 server (using EM).
> > The DB had daily backups and several transaction log backups during the
> > day. This has been going on for the last 2-3 years and the number of
> > records in the msdb backup system tables for historical purposes was huge.
> > When EM drops a DB and begins to delete the historical information about
> > the backups, it called the sp_delete_backup_history procedure. If there
> > was ever a procedure that needed indexes, this is it. The procedure had
> > been running all night and a trace showed it doing several table scans
> > using "lots" of cursors.
> > After I added the indexes the job finished w/i 10 minutes. We dropped
> > another database that would have had the same backup history and the
> > procedure finished w/i 15 minutes.
> >
> > Thanks for the confirmation -- cw
> >
> >
> > "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> > news:%235GjJwqAHHA.996@.TK2MSFTNGP02.phx.gbl...
> >>I do it all the time. One side effect of constant build testing is that
> >>you don't run something for a long time and see some of the life cycle
> >>issues that we see in real life. Just don't add keys or constraints, only
> >>indexes and you should be fine.
> >>
> >> Here is the set I use:
> >>
> >> /************************************************************************
> >>
> >> * *
> >>
> >> * Title: msdb index creation *
> >>
> >> * Author: Geoff N. Hiten *
> >>
> >> * Purpose: Index msdb database *
> >>
> >> * Date: 12/12/2005 *
> >>
> >> * Modifications: *
> >>
> >> * *
> >>
> >> * 06-22-2001 *
> >>
> >> * Sample Entry *
> >>
> >> * *
> >>
> >> * *
> >>
> >> ************************************************************************/
> >>
> >> use
> >>
> >> msdb
> >> go
> >>
> >> --backupset
> >>
> >> Create
> >>
> >> index IX_backupset_backup_set_id on backupset (backup_set_id)
> >> go
> >>
> >> Create
> >>
> >> index IX_backupset_backup_set_uuid on backupset (backup_set_uuid)
> >> go
> >>
> >> Create
> >>
> >> index IX_backupset_media_set_id on backupset (media_set_id)
> >> go
> >>
> >> Create
> >>
> >> index IX_backupset_backup_finish_date on backupset (backup_finish_date)
> >> go
> >>
> >> Create
> >>
> >> index IX_backupset_backup_start_date on backupset (backup_start_date)
> >> go
> >>
> >> --backupmediaset
> >>
> >> Create
> >>
> >> index IX_backupmediaset_media_set_id on backupmediaset (media_set_id)
> >> go
> >>
> >> --backupfile
> >>
> >> Create
> >>
> >> index IX_backupfile_backup_set_id on backupfile (backup_set_id)
> >> go
> >>
> >> --backupmediafamily
> >>
> >> Create
> >>
> >> index IX_backupmediafamily_media_set_id on backupmediafamily
> >> (media_set_id)
> >> go
> >>
> >> --restorehistory
> >>
> >> Create
> >>
> >> index IX_restorehistory_restore_history_id on restorehistory
> >> (restore_history_id)
> >> go
> >>
> >> Create
> >>
> >> index IX_restorehistory_backup_set_id on restorehistory (backup_set_id)
> >> go
> >>
> >> --restorefile
> >>
> >> Create
> >>
> >> index IX_restorefile_restore_history_id on restorefile
> >> (restore_history_id)
> >> go
> >>
> >> --restorefilegroup
> >>
> >> Create
> >>
> >> index IX_restorefilegroup_restore_history_id on restorefilegroup
> >> (restore_history_id)
> >> go
> >>
> >>
> >>
> >> "cw" <cw@.3mc.com> wrote in message
> >> news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
> >> WHat would be the danger of adding a couple of indexes to the msdb
> >> system tables (particularly the backup/restore jobs tables)? I know
> >> that a hotfix or sp update may drop the tables, but if we are staying on
> >> sp3 or sp4 for now, would it cause a sytem wide issue?
> >>
> >> This is for SQL2000 on a Win2003 server.
> >>
> >> Thanks
> >>
> >>
> >>
> >
> >