MSDB database restore: tables

Showing posts with label tables. Show all posts

Monday, March 26, 2012

MSDE 2000 problems with linked server

I have set up several servers with MSDE 2000 sp4, have created tables in
these databases, can access, read, write data to the database through win sql
and asp. I have set up a linked server to an oracle database and created a
linkedsrvlogin. I can select data from my oracle database throught SQL using
the linked server. All of my servers are working fine except one. Onne
particular server is taking an extrememly long time to retrive certain data.
For example, I have one server that can get 31000 rows from my linked server
in 14 seconds, using anohter server, it takes almost 3 mins to get back 773
rows from the same table.
Any ideas? Its driving me crazy!!!
Hi Lloyd
Have you tried adding a host file entry to the oracle server on the machine
which is slower than the others, it's a stab in the dark but your one machine
could be talking longer to resolve the name of the oracle db.
Regards
charl
http://www.sqlserver.co.za
"Lloyd Harrison" wrote:

> I have set up several servers with MSDE 2000 sp4, have created tables in
> these databases, can access, read, write data to the database through win sql
> and asp. I have set up a linked server to an oracle database and created a
> linkedsrvlogin. I can select data from my oracle database throught SQL using
> the linked server. All of my servers are working fine except one. Onne
> particular server is taking an extrememly long time to retrive certain data.
> For example, I have one server that can get 31000 rows from my linked server
> in 14 seconds, using anohter server, it takes almost 3 mins to get back 773
> rows from the same table.
> Any ideas? Its driving me crazy!!!

MSDE 2000 problems with linked server

I have set up several servers with MSDE 2000 sp4, have created tables in
these databases, can access, read, write data to the database through win sql
and asp. I have set up a linked server to an oracle database and created a
linkedsrvlogin. I can select data from my oracle database throught SQL using
the linked server. All of my servers are working fine except one. Onne
particular server is taking an extrememly long time to retrive certain data.
For example, I have one server that can get 31000 rows from my linked server
in 14 seconds, using anohter server, it takes almost 3 mins to get back 773
rows from the same table.
Any ideas? Its driving me crazy!!!Hi Lloyd
Have you tried adding a host file entry to the oracle server on the machine
which is slower than the others, it's a stab in the dark but your one machine
could be talking longer to resolve the name of the oracle db.
Regards
charl
http://www.sqlserver.co.za
"Lloyd Harrison" wrote:
> I have set up several servers with MSDE 2000 sp4, have created tables in
> these databases, can access, read, write data to the database through win sql
> and asp. I have set up a linked server to an oracle database and created a
> linkedsrvlogin. I can select data from my oracle database throught SQL using
> the linked server. All of my servers are working fine except one. Onne
> particular server is taking an extrememly long time to retrive certain data.
> For example, I have one server that can get 31000 rows from my linked server
> in 14 seconds, using anohter server, it takes almost 3 mins to get back 773
> rows from the same table.
> Any ideas? Its driving me crazy!!!

MSDE 2000 problems with linked server

I have set up several servers with MSDE 2000 sp4, have created tables in
these databases, can access, read, write data to the database through win sq
l
and asp. I have set up a linked server to an oracle database and created a
linkedsrvlogin. I can select data from my oracle database throught SQL usin
g
the linked server. All of my servers are working fine except one. Onne
particular server is taking an extrememly long time to retrive certain data.
For example, I have one server that can get 31000 rows from my linked server
in 14 seconds, using anohter server, it takes almost 3 mins to get back 773
rows from the same table.
Any ideas? Its driving me crazy!!!Hi Lloyd
Have you tried adding a host file entry to the oracle server on the machine
which is slower than the others, it's a stab in the dark but your one machin
e
could be talking longer to resolve the name of the oracle db.
Regards
charl
http://www.sqlserver.co.za
"Lloyd Harrison" wrote:

> I have set up several servers with MSDE 2000 sp4, have created tables in
> these databases, can access, read, write data to the database through win
sql
> and asp. I have set up a linked server to an oracle database and created a
> linkedsrvlogin. I can select data from my oracle database throught SQL us
ing
> the linked server. All of my servers are working fine except one. Onne
> particular server is taking an extrememly long time to retrive certain dat
a.
> For example, I have one server that can get 31000 rows from my linked serv
er
> in 14 seconds, using anohter server, it takes almost 3 mins to get back 77
3
> rows from the same table.
> Any ideas? Its driving me crazy!!!

Friday, March 9, 2012

MSDE - Master database

HI all,
What is supposed to be in te master database, and how can I fix it.
Seems to me that I messed up and have some tables, lviews, etc,looks like
from northwind database.
I think it happened when I tried the upsize wisard in access and made a big
booboo
Robert
Restore the Master database. You backed it up didn't you?
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Robert Bravery" <me@.u.com> wrote in message
news:eXzllihtEHA.3860@.TK2MSFTNGP09.phx.gbl...
> HI all,
> What is supposed to be in te master database, and how can I fix it.
> Seems to me that I messed up and have some tables, lviews, etc,looks like
> from northwind database.
> I think it happened when I tried the upsize wisard in access and made a
> big
> booboo
> Robert
>
|||HI,
I am just testing at the moment, only installed mSDE a few days agao.
Is there no script somewhere that I can delete and recreate.
Being new to MSDE I have no idea of the purpose of this database
Thanks
Robert
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:e2P6xkktEHA.4044@.TK2MSFTNGP09.phx.gbl...
> Restore the Master database. You backed it up didn't you?
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> __________________________________
> "Robert Bravery" <me@.u.com> wrote in message
> news:eXzllihtEHA.3860@.TK2MSFTNGP09.phx.gbl...
like
>
|||other option would be to rebuild the master database using the rebuildm
utiliy . This option is used if u do not have any backup of the master
database.
|||hi Girish,
"Girish Sundaram" <girishs@.microsoft.com> ha scritto nel messaggio
news:RgKxw65tEHA.2520@.cpmsftngxa10.phx.gbl
> other option would be to rebuild the master database using the
> rebuildm utiliy . This option is used if u do not have any backup of
> the master database.
unfortunately you do not have access to "rebuildm" utility in MSDE..

Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

MSDE - list databases, describe tables, migrate from Access?

I am new to SQL Server but have been using Access for a while. I just
installed MSDE (latest version) to evaluate it for use in small business
systems rather than Access peer-to-peer. Just a few quick questions:
1) I can connect to MSDE from an Access db by linking, but I think I am
doing this through ODBC (using the link table wizard in Access). Is this
the correct way?
2) In goofing around I used 'osql' to do a bunch of create table and insert
statements to create some test data but I didn't save these statements. Now
I can't see how to get the list of databases, tables, and columns that I
created. Is there anything like 'show databases', 'show tables', or
'describe <tablename>' that will output a description of what I've created?
3) Is there any simple way to take a database created in MS-Access and
convert it to SQL Server format? I.e. can I create my tables in Access's
nice user interface and then move them over to MSDE?
Thanks,
Doug MHi,
1. You are in the right tract if you are using MSAccess .
2. Go into SQL prompt using
OSQL -Usa -Ppassword -S serve_name (Enter Key)
-- To display list of databases
1>sp_databases (enter)
2>go (enter)
-- To list the tables in a database
1>use dbname (enter)
2>go (enter)
1>sp_tables (enter)
2>go (enter)
-- To see the table structure
1>use dbname (enter)
2>go (enter)
1>sp_help table_name (enter)
2>go (enter)
This list goes on and on... See books online for moredetails on the SQL
Server TSQL commands.
3. Use can use DTS , but DTS wont be coming with MSDE. Other approaches
are manual.
a. Create the table in MSDE manually
b. Generate the comm seperated text from MSACCESS for each table
c. Use BCP IN or BULK INSERT utility in sql server MSDE to load into
tables.
Thanks
Hari
MCDBA
"user" <user@.nowhere.com> wrote in message
news:40d50f01$0$18672$afc38c87@.news.optusnet.com.au...
> I am new to SQL Server but have been using Access for a while. I just
> installed MSDE (latest version) to evaluate it for use in small business
> systems rather than Access peer-to-peer. Just a few quick questions:
> 1) I can connect to MSDE from an Access db by linking, but I think I am
> doing this through ODBC (using the link table wizard in Access). Is this
> the correct way?
> 2) In goofing around I used 'osql' to do a bunch of create table and
insert
> statements to create some test data but I didn't save these statements.
Now
> I can't see how to get the list of databases, tables, and columns that I
> created. Is there anything like 'show databases', 'show tables', or
> 'describe <tablename>' that will output a description of what I've
created?
> 3) Is there any simple way to take a database created in MS-Access and
> convert it to SQL Server format? I.e. can I create my tables in Access's
> nice user interface and then move them over to MSDE?
> Thanks,
> Doug M
>|||That's great, thanks.
Doug M
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OxwcvkqVEHA.3596@.tk2msftngp13.phx.gbl...
> Hi,
> 1. You are in the right tract if you are using MSAccess .
> 2. Go into SQL prompt using
> OSQL -Usa -Ppassword -S serve_name (Enter Key)
> -- To display list of databases
> 1>sp_databases (enter)
> 2>go (enter)
> -- To list the tables in a database
> 1>use dbname (enter)
> 2>go (enter)
> 1>sp_tables (enter)
> 2>go (enter)
> -- To see the table structure
> 1>use dbname (enter)
> 2>go (enter)
> 1>sp_help table_name (enter)
> 2>go (enter)
>
> This list goes on and on... See books online for moredetails on the SQL
> Server TSQL commands.
>
> 3. Use can use DTS , but DTS wont be coming with MSDE. Other approaches
> are manual.
> a. Create the table in MSDE manually
> b. Generate the comm seperated text from MSACCESS for each table
> c. Use BCP IN or BULK INSERT utility in sql server MSDE to load into
> tables.
> Thanks
> Hari
> MCDBA
>
> "user" <user@.nowhere.com> wrote in message
> news:40d50f01$0$18672$afc38c87@.news.optusnet.com.au...
> > I am new to SQL Server but have been using Access for a while. I
just
> > installed MSDE (latest version) to evaluate it for use in small business
> > systems rather than Access peer-to-peer. Just a few quick questions:
> >
> > 1) I can connect to MSDE from an Access db by linking, but I think I am
> > doing this through ODBC (using the link table wizard in Access). Is
this
> > the correct way?
> >
> > 2) In goofing around I used 'osql' to do a bunch of create table and
> insert
> > statements to create some test data but I didn't save these statements.
> Now
> > I can't see how to get the list of databases, tables, and columns that I
> > created. Is there anything like 'show databases', 'show tables', or
> > 'describe <tablename>' that will output a description of what I've
> created?
> >
> > 3) Is there any simple way to take a database created in MS-Access and
> > convert it to SQL Server format? I.e. can I create my tables in
Access's
> > nice user interface and then move them over to MSDE?
> >
> > Thanks,
> >
> > Doug M
> >
> >
>|||Ben Forta's book "SQL in 10 Minutes" is a most valuable desk-side
reference and I've recently also come to learn the same can be said
of his most recent work "Regular Expressions in 10 Minutes."
Highly recommended and each costs less than a large pepperoni pizza ;-)
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
"user" <user@.nowhere.com> wrote in message
news:40d5fa03$0$18671$afc38c87@.news.optusnet.com.au...
> That's great, thanks.
> Doug M
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OxwcvkqVEHA.3596@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > 1. You are in the right tract if you are using MSAccess .
> >
> > 2. Go into SQL prompt using
> >
> > OSQL -Usa -Ppassword -S serve_name (Enter Key)
> >
> > -- To display list of databases
> >
> > 1>sp_databases (enter)
> > 2>go (enter)
> >
> > -- To list the tables in a database
> >
> > 1>use dbname (enter)
> > 2>go (enter)
> > 1>sp_tables (enter)
> > 2>go (enter)
> >
> > -- To see the table structure
> >
> > 1>use dbname (enter)
> > 2>go (enter)
> > 1>sp_help table_name (enter)
> > 2>go (enter)
> >
> >
> >
> > This list goes on and on... See books online for moredetails on the SQL
> > Server TSQL commands.
> >
> >
> >
> > 3. Use can use DTS , but DTS wont be coming with MSDE. Other approaches
> > are manual.
> >
> > a. Create the table in MSDE manually
> > b. Generate the comm seperated text from MSACCESS for each table
> > c. Use BCP IN or BULK INSERT utility in sql server MSDE to load into
> > tables.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> > "user" <user@.nowhere.com> wrote in message
> > news:40d50f01$0$18672$afc38c87@.news.optusnet.com.au...
> > > I am new to SQL Server but have been using Access for a while. I
> just
> > > installed MSDE (latest version) to evaluate it for use in small business
> > > systems rather than Access peer-to-peer. Just a few quick questions:
> > >
> > > 1) I can connect to MSDE from an Access db by linking, but I think I am
> > > doing this through ODBC (using the link table wizard in Access). Is
> this
> > > the correct way?
> > >
> > > 2) In goofing around I used 'osql' to do a bunch of create table and
> > insert
> > > statements to create some test data but I didn't save these statements.
> > Now
> > > I can't see how to get the list of databases, tables, and columns that I
> > > created. Is there anything like 'show databases', 'show tables', or
> > > 'describe <tablename>' that will output a description of what I've
> > created?
> > >
> > > 3) Is there any simple way to take a database created in MS-Access and
> > > convert it to SQL Server format? I.e. can I create my tables in
> Access's
> > > nice user interface and then move them over to MSDE?
> > >
> > > Thanks,
> > >
> > > Doug M
> > >
> > >
> >
> >
>

MSDE

I looking for some guidance here. I have database which has exceeded the 2 gig limit. I deleted some old tables in an attempt to acquire some space. This worked out. However when I look at the size of my *.mdf it still appears in excess of the 2 gigs . I know this isn't true since I deleted 500 megs of stuff. How do i reclaim the space (get the file size to report correctly) or do I need to even worry about it.

AndyYou might try first backing up the database and then running the "Shrink" command. In Enterprise Manager Select the database, right click and select "Shrink..." from the list of commands. If you don't have Enterprise Manager you can try this command through osql or equivalent.


DBCC SHRINKDATABASE ([YourDatabaseName], 0)

|||Thanks for your reply and help. I was just about to attempt it when I realized from reading another article on the subject than MSDE dynamically recalculates and adjusts files and indices. Turns out all I had to do was be patient and it took care of itself. This apparently is different functionality from the full blown version of SQL2000. Anyhow, thanks
Andy

Wednesday, March 7, 2012

msdb's sysjobs tables

Please list the cases when the contents of sysjobhistory table would be
deleted.
thanks
--
Cathy BOne way
from SQL server agent--> Properties-->Job System click on Clear Log
http://sqlservercode.blogspot.com/|||Yes,
But I am looking as to why there's no history on a particular job.
Any other ways?
--
Cathy B
"SQL" wrote:

> One way
> from SQL server agent--> Properties-->Job System click on Clear Log
> http://sqlservercode.blogspot.com/
>|||yes
right click on a job-->view job history-->clear all
http://sqlservercode.blogspot.com/|||Thank you.
I am not looking to clear the table.
I am looking for job history that I can't find.
The job was scheduled twice a week to run for a year.
There appears to be no history on it.
And I don't believe anybody here would go and clear the log.
Although anything is possible.
--
Cathy B
"SQL" wrote:

> yes
> right click on a job-->view job history-->clear all
> http://sqlservercode.blogspot.com/
>|||Is your maximum job history log size (rows) set to 1000 and you have
reached that number a year ago?|||when somebody does "delete from sysjobhistory"
Cathy Boehm wrote:
> Please list the cases when the contents of sysjobhistory table would be
> deleted.
> thanks
> --
> Cathy B

msdb's sysjobs tables

Please list the cases when the contents of sysjobhistory table would be
deleted.
thanks
Cathy B
One way
from SQL server agent--> Properties-->Job System click on Clear Log
http://sqlservercode.blogspot.com/
|||Yes,
But I am looking as to why there's no history on a particular job.
Any other ways?
Cathy B
"SQL" wrote:

> One way
> from SQL server agent--> Properties-->Job System click on Clear Log
> http://sqlservercode.blogspot.com/
>
|||yes
right click on a job-->view job history-->clear all
http://sqlservercode.blogspot.com/
|||Thank you.
I am not looking to clear the table.
I am looking for job history that I can't find.
The job was scheduled twice a week to run for a year.
There appears to be no history on it.
And I don't believe anybody here would go and clear the log.
Although anything is possible.
Cathy B
"SQL" wrote:

> yes
> right click on a job-->view job history-->clear all
> http://sqlservercode.blogspot.com/
>
|||Is your maximum job history log size (rows) set to 1000 and you have
reached that number a year ago?
|||when somebody does "delete from sysjobhistory"
Cathy Boehm wrote:
> Please list the cases when the contents of sysjobhistory table would be
> deleted.
> thanks
> --
> Cathy B

msdb's sysjobs tables

Please list the cases when the contents of sysjobhistory table would be
deleted.
thanks
--
Cathy BOne way
from SQL server agent--> Properties-->Job System click on Clear Log
http://sqlservercode.blogspot.com/|||Yes,
But I am looking as to why there's no history on a particular job.
Any other ways?
--
Cathy B
"SQL" wrote:
> One way
> from SQL server agent--> Properties-->Job System click on Clear Log
> http://sqlservercode.blogspot.com/
>|||yes
right click on a job-->view job history-->clear all
http://sqlservercode.blogspot.com/|||Thank you.
I am not looking to clear the table.
I am looking for job history that I can't find.
The job was scheduled twice a week to run for a year.
There appears to be no history on it.
And I don't believe anybody here would go and clear the log.
Although anything is possible.
--
Cathy B
"SQL" wrote:
> yes
> right click on a job-->view job history-->clear all
> http://sqlservercode.blogspot.com/
>|||Is your maximum job history log size (rows) set to 1000 and you have
reached that number a year ago?|||when somebody does "delete from sysjobhistory"
Cathy Boehm wrote:
> Please list the cases when the contents of sysjobhistory table would be
> deleted.
> thanks
> --
> Cathy B

Saturday, February 25, 2012

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

Monday, March 26, 2012

MSDE 2000 problems with linked server

MSDE 2000 problems with linked server

MSDE 2000 problems with linked server

Friday, March 9, 2012

MSDE - Master database

MSDE - list databases, describe tables, migrate from Access?

MSDE - list databases, describe tables, migrate from Access?

MSDE - list databases, describe tables, migrate from Access?

MSDE

Wednesday, March 7, 2012

msdb's sysjobs tables

msdb's sysjobs tables

msdb's sysjobs tables

Saturday, February 25, 2012

MSDB question

MSDB question

MSDB question

MSDB database restore

Blog Archive

About Me