MSDE 2000 with Access 2003: The First steps

Finally got to the point where I can connect to MSDE from Access. If you are setting up MSDE for the first time, you may find a couple useful points in the following.
MSDE 2000 with Access 2003: The First steps.
1) install MSDE from Office CD (need to locate in MSDE directory by browsing and start it manually. Read KB article 319930 first);
2) when installing, need to specify "sa" (system admin) password which is not (does not have to be anyway) the same as Windows Administrator's password. It is required in order not to leave sysadmin password blank which would leave a security hole;
3) if you are using all the correct switches with the 'setup' command, the mixed login mode will be allowed. The mixed login mode lets you login from non-admin account by using MSDE login. I did not use any switches so had to make the adjustments in the r
egistry. Very easy, but I still had to locate the required key which was not where it is supposed to be judging from KB article 319930, but quite near it.
4) restart and check that 2 new services are running: MSSQLSERVER and SQLSERVERAGENT.
5) restart the PC and log in under (Windows) administrator account (does not need to be THE "Administrator", just any account with admin privileges)
6) test the connection to MDSE by going to command line, typing "osql -E" (no " needed). You should see ">1" which means MDSE is running.
7) Now this is the $100 trick: type "sp_addlogin [BUILTIN\POWER USERS]", <ENTER>, "GO". wait for some reaction (something like 2 rows affected)
8) Here comes another $100 trick: type "grant all", <ENTER>, "to [BUILTIN\POWER USERS]", <ENTER>, "go".
Now you can go back to your every day Windows login (I assume you are using an account from Power Users group, aren't you?), start Access, create a new .adp file and connect your Access .adp file to MSDE using "Trusted Connection" or "Use Windows NT integ
rated security".
Oh by the way you can type "exit" from command line to stop your connection to MSDE when you feel like.
You can also submit commands and scripts via OSQL directly from the command
line. I thought from your previous post that you were trying to 'assign
(SQL) sysadmin permissions to a newly created (SQL) account.' I.e., your
SQL account was already created and you were trying to put it in the
sysadmin server role. By the way,
$50 Discount - GRANT grants database-specific permissions to users. So if
you have more than one Database, you need to use GRANT on all databases and
database objects you want the user to have access to.
$0.02 worth - sp_addrolemember and sp_addsrvrolemember adds users to
server-wide roles, like "sysadmin." GRANT is database object specific, so
it works only on individual databases and database objects (tables, etc.)
Cheers,
Michael C.
"Lolik" <Lolik@.discussions.microsoft.com> wrote in message
news:BDFB3614-78ED-4DB2-8468-7C78073A7906@.microsoft.com...
> Finally got to the point where I can connect to MSDE from Access. If you
are setting up MSDE for the first time, you may find a couple useful points
in the following.
> MSDE 2000 with Access 2003: The First steps.
> 1) install MSDE from Office CD (need to locate in MSDE directory by
browsing and start it manually. Read KB article 319930 first);
> 2) when installing, need to specify "sa" (system admin) password which is
not (does not have to be anyway) the same as Windows Administrator's
password. It is required in order not to leave sysadmin password blank which
would leave a security hole;
> 3) if you are using all the correct switches with the 'setup' command, the
mixed login mode will be allowed. The mixed login mode lets you login from
non-admin account by using MSDE login. I did not use any switches so had to
make the adjustments in the registry. Very easy, but I still had to locate
the required key which was not where it is supposed to be judging from KB
article 319930, but quite near it.
> 4) restart and check that 2 new services are running: MSSQLSERVER and
SQLSERVERAGENT.
> 5) restart the PC and log in under (Windows) administrator account (does
not need to be THE "Administrator", just any account with admin privileges)
> 6) test the connection to MDSE by going to command line, typing "osql -E"
(no " needed). You should see ">1" which means MDSE is running.
> 7) Now this is the $100 trick: type "sp_addlogin [BUILTIN\POWER USERS]",
<ENTER>, "GO". wait for some reaction (something like 2 rows affected)
> 8) Here comes another $100 trick: type "grant all", <ENTER>, "to
[BUILTIN\POWER USERS]", <ENTER>, "go".
> Now you can go back to your every day Windows login (I assume you are
using an account from Power Users group, aren't you?), start Access, create
a new .adp file and connect your Access .adp file to MSDE using "Trusted
Connection" or "Use Windows NT integrated security".
> Oh by the way you can type "exit" from command line to stop your
connection to MSDE when you feel like.
|||$0.02 worth - sp_addrolemember and sp_addsrvrolemember adds users to
server-wide roles, like "sysadmin." GRANT is database object specific, so
it works only on individual databases and database objects (tables, etc.)
__________________
Do not underestimate your input Michael!
Guess I should run sp_addsrvrolemember too. Still after having used GRANT as above I was able to create a new SQL database and connect to it, at the time a new Access .adp file was set up.
The $50 tag referred to using [BUILTIN\POWER USERS] as a login. Could not find it mentioned anywhere. Maybe it is obvious to everyone but me but figuring out what [Corporate\Bobj] is in XP language was not straightforward to me at all.
|||Yes, you can grant User Groups access rights to your SQL Server One of
the benefits of using Integrated Security for your SQL Server logins. The
built-in server roles, like sysadmin grant general permissions server-wide,
and include various other little niceties. Since your user created the 2nd
database, that user is the database owner (dbo) for that database, so that
user will have all the rights associated with that database. Server Roles
make it a little easier to admin multiple databases.
Cheers,
Michael C.

> Do not underestimate your input Michael!
> Guess I should run sp_addsrvrolemember too. Still after having used GRANT
as above I was able to create a new SQL database and connect to it, at the
time a new Access .adp file was set up.
sql

msde

I recently joined a company where they have couple of MSDEs that were
installed as part of the software application package. Do they need to be
administered as well alongwith the other Standard and Enterprise SQL Servers
installations?
sharman,
The answer about how much administration depends on how these MSDE servers
are being used.
If they hold critical data, then yes they need to be administered along with
the other SQL Servers. If there are security issues related to access, these
need to be managed as well.
If the application simply uses these databases as a temporary workarea and
can easily rebuild the data in case of problems, then the management is
less. It would still be good to have database backups that could restore
the metadata.
You need to understand what is going on with the MSDEs and then you can make
a good decision.
RLF
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:1B413580-5393-405C-81CD-0CECD6D4DA83@.microsoft.com...
>I recently joined a company where they have couple of MSDEs that were
> installed as part of the software application package. Do they need to be
> administered as well alongwith the other Standard and Enterprise SQL
> Servers
> installations?

MSDE

Hi there,
I have a couple of questions related to MSDE 2000.
1. Is there only one SA account for all instances that may be present on a system or each instance has its own SA account and password?
2. When there is an MSDE instance already installed and it is set to Windows Authentication mode, are the instances installed later going to have to use Windows Authentication mode or they could be in Mixed mode?
Thank you for your help,
GDD1499
GDD1499,
Each instance of MSDE 2000 is completely independent and separate from all
other instances. So, to be specific, each instance of MSDE 2000 has its own
sa account and password. Additionally, one (or more) instances of MSDE 2000
can use Windows Authentication mode, while other instances on the same box
may be using Mixed Mode Authentication.
Chief Tenaya
"GDD1499" <anonymous@.discussions.microsoft.com> wrote in message
news:A38DA9C9-13F4-4C67-B911-74F24CD9D9DF@.microsoft.com...
> Hi there,
> I have a couple of questions related to MSDE 2000.
> 1. Is there only one SA account for all instances that may be present on a
system or each instance has its own SA account and password?
> 2. When there is an MSDE instance already installed and it is set to
Windows Authentication mode, are the instances installed later going to have
to use Windows Authentication mode or they could be in Mixed mode?
> Thank you for your help,
> GDD1499

msde

I recently joined a company where they have couple of MSDEs that were
installed as part of the software application package. Do they need to be
administered as well alongwith the other Standard and Enterprise SQL Servers
installations?sharman,
The answer about how much administration depends on how these MSDE servers
are being used.
If they hold critical data, then yes they need to be administered along with
the other SQL Servers. If there are security issues related to access, these
need to be managed as well.
If the application simply uses these databases as a temporary workarea and
can easily rebuild the data in case of problems, then the management is
less. It would still be good to have database backups that could restore
the metadata.
You need to understand what is going on with the MSDEs and then you can make
a good decision.
RLF
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:1B413580-5393-405C-81CD-0CECD6D4DA83@.microsoft.com...
>I recently joined a company where they have couple of MSDEs that were
> installed as part of the software application package. Do they need to be
> administered as well alongwith the other Standard and Enterprise SQL
> Servers
> installations?

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...
>|||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...
>|||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:[vbcol=seagreen]
> 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...

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.
Thanks
cw
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...
>
|||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...
>
|||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:[vbcol=seagreen]
> 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...

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
> >>
> >>
> >>
> >
> >