MSDB database restore: databases

Showing posts with label databases. 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 23, 2012

MSDE 2000 database setup trouble

Hello
I am running a stand-alone test environment for an application in XP Pro
which I can only use MSDE to manage my databases.
I've successfully installed msde and am finally able to connect using 'osql
-Usa -Ppassword -S (local)\testmachine' , because I can get the '1>' command
prompt.
My trouble is when I try to create and/or restore a database for the test
application to use I'm running into trouble. For example enter this :
1> USE master
2> RESTORE DATABASE master FROM DISK='C:\master\master_db2.BAK'
3>GO
and I get an error message telling me that "RESTORE DATABASE must be used in
single user mode when trying to restore the master database" ...... I also
get a similar message when trying to create and restore a database called
'public'.
how do I successfully get into 'single user mode' ?
hi,
APB wrote:
> Hello
> I am running a stand-alone test environment for an application in XP
> Pro which I can only use MSDE to manage my databases.
> I've successfully installed msde and am finally able to connect using
> 'osql -Usa -Ppassword -S (local)\testmachine' , because I can get the
> '1>' command prompt.
> My trouble is when I try to create and/or restore a database for the
> test application to use I'm running into trouble. For example enter
> this :
> 1> USE master
> 2> RESTORE DATABASE master FROM DISK='C:\master\master_db2.BAK'
> 3>GO
> and I get an error message telling me that "RESTORE DATABASE must be
> used in single user mode when trying to restore the master database"
> ...... I also get a similar message when trying to create and
> restore a database called 'public'.
> how do I successfully get into 'single user mode' ?
have a look at http://msdn2.microsoft.com/en-us/library/ms180965.aspx ..
typically you open a command window, navigate to the \Binn folder of the
instance you want to start and execute something like
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\>sqlservr.exe -s
InstanceName -m
this will start the instance in single user modo, so that you'll be able to
restore the master database...
to restore "normal user's" database(s) the single user mode is not required,
but the database(s) to be restored must not be in use... so no active
connections must be running against the database(s) to be restored..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.bizhttp://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
-- remove DMO to reply

MSDE 2000 connectivity problem

Hi,
I have installed MSDE 2000 into my desktop machine.I can
connect from other machines to my desktop and accessing
databases using Query Analyser, Enterperise manager,
Profiler.but I didn't connect from ASP.NET application
using as "connectionstring".
Earlier it was worked.now its not working.
Send me your valuable reply ASAP.
Thanks and regards,
RafiqHow does your connection string look like?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rafiq" <anonymous@.discussions.microsoft.com> wrote in message
news:088801c3a9ab$02032c50$a401280a@.phx.gbl...
> Hi,
> I have installed MSDE 2000 into my desktop machine.I can
> connect from other machines to my desktop and accessing
> databases using Query Analyser, Enterperise manager,
> Profiler.but I didn't connect from ASP.NET application
> using as "connectionstring".
> Earlier it was worked.now its not working.
> Send me your valuable reply ASAP.
> Thanks and regards,
> Rafiq

Monday, March 19, 2012

MSDE / SQL Server

rDear all,
I have been reading the latest post on the exchangeability between MSDE and
SQL Server 2000.
MSDE databases are exchangeable with SQL Server databases, both direction,
except when the database size is greater than 2GB which is the limit for MSDE.
When I move a SQL Server database to MSDE, I have to make a backup from the
database on the sql server and restore it in msde using RESTORE DATABASE
FROM... and it works the same the other direction.
I have downloaded the SQLEVAL.EXE and I am going to install it on my laptop,
on which MSDE is already running. From the discussions running both on a
windows 2000 server should be possible. Can I run both on my laptop? Upon
installing the evaluation version next to MSDE are there any peticular things
I have to take into consideration?
regards,
DannyHi
No issues, but make sure that you install SP3a against the SQL Server 2000
installation.
The MSDE might be the default instance so the SQL Server 2000 installation
will then need to be a named instance.
Regards
Mike
"Danny Gaethofs" wrote:
> rDear all,
> I have been reading the latest post on the exchangeability between MSDE and
> SQL Server 2000.
> MSDE databases are exchangeable with SQL Server databases, both direction,
> except when the database size is greater than 2GB which is the limit for MSDE.
> When I move a SQL Server database to MSDE, I have to make a backup from the
> database on the sql server and restore it in msde using RESTORE DATABASE
> FROM... and it works the same the other direction.
> I have downloaded the SQLEVAL.EXE and I am going to install it on my laptop,
> on which MSDE is already running. From the discussions running both on a
> windows 2000 server should be possible. Can I run both on my laptop? Upon
> installing the evaluation version next to MSDE are there any peticular things
> I have to take into consideration?
> regards,
> Danny|||Dear Mike,
I am a bit confused about what you mean to tell me with default and named
instance. This is probably due to my lack of knowledge of sql server.
How can I indicate or see which one is the default instance?
regards,
Danny
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> No issues, but make sure that you install SP3a against the SQL Server 2000
> installation.
> The MSDE might be the default instance so the SQL Server 2000 installation
> will then need to be a named instance.
> Regards
> Mike
> "Danny Gaethofs" wrote:
> > rDear all,
> >
> > I have been reading the latest post on the exchangeability between MSDE and
> > SQL Server 2000.
> >
> > MSDE databases are exchangeable with SQL Server databases, both direction,
> > except when the database size is greater than 2GB which is the limit for MSDE.
> >
> > When I move a SQL Server database to MSDE, I have to make a backup from the
> > database on the sql server and restore it in msde using RESTORE DATABASE
> > FROM... and it works the same the other direction.
> >
> > I have downloaded the SQLEVAL.EXE and I am going to install it on my laptop,
> > on which MSDE is already running. From the discussions running both on a
> > windows 2000 server should be possible. Can I run both on my laptop? Upon
> > installing the evaluation version next to MSDE are there any peticular things
> > I have to take into consideration?
> >
> > regards,
> > Danny|||You can go to the properties tab in EM of the SQL server and that will list
the name of your server. U can also go to the services in control panel and
look at the MSSQL service to find out the name. Usually the first instance
installed is your server name.
"Danny Gaethofs" wrote:
> Dear Mike,
> I am a bit confused about what you mean to tell me with default and named
> instance. This is probably due to my lack of knowledge of sql server.
> How can I indicate or see which one is the default instance?
> regards,
> Danny
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > No issues, but make sure that you install SP3a against the SQL Server 2000
> > installation.
> >
> > The MSDE might be the default instance so the SQL Server 2000 installation
> > will then need to be a named instance.
> >
> > Regards
> > Mike
> >
> > "Danny Gaethofs" wrote:
> >
> > > rDear all,
> > >
> > > I have been reading the latest post on the exchangeability between MSDE and
> > > SQL Server 2000.
> > >
> > > MSDE databases are exchangeable with SQL Server databases, both direction,
> > > except when the database size is greater than 2GB which is the limit for MSDE.
> > >
> > > When I move a SQL Server database to MSDE, I have to make a backup from the
> > > database on the sql server and restore it in msde using RESTORE DATABASE
> > > FROM... and it works the same the other direction.
> > >
> > > I have downloaded the SQLEVAL.EXE and I am going to install it on my laptop,
> > > on which MSDE is already running. From the discussions running both on a
> > > windows 2000 server should be possible. Can I run both on my laptop? Upon
> > > installing the evaluation version next to MSDE are there any peticular things
> > > I have to take into consideration?
> > >
> > > regards,
> > > Danny

MSDE & WMSDE

What is the diffrence between MSDE 2000 and WMSDE? I have two applications
that require these databases and I am concerned there might be issues in
using them on the same server:
SmarterTicket / MSDE 2000
Windows SharePoint Services / WMSDE
Thanks - Jody
hi Jody,
Jody wrote:
> What is the diffrence between MSDE 2000 and WMSDE? I have two
> applications that require these databases and I am concerned there
> might be issues in using them on the same server:
> SmarterTicket / MSDE 2000
> Windows SharePoint Services / WMSDE
> Thanks - Jody
WMSDE is a customized version of MSDE 2000 distributed along with and only
available with SharePoint...
it has been modified to fit SharePoint needs and you should avoid using it
for your own databases..
I do not have a WMSDE eula available, but I think it prohibits a shared use
with other apps and related databases..
install 2 separated instances, 1 of WMSDE to be used by SharePoint and
another MSDE one to be used by your other apps.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
In reference to you comment:
"...it has been modified to fit SharePoint needs and you should avoid using
it for your own databases..."
Do you mean that I should avoid using WMSDE with WSS or along side of
SmarterTicket becasue it uses MSDE?
Thanks - Jody

> hi Jody,
> Jody wrote:
> WMSDE is a customized version of MSDE 2000 distributed along with and only
> available with SharePoint...
> it has been modified to fit SharePoint needs and you should avoid using it
> for your own databases..
> I do not have a WMSDE eula available, but I think it prohibits a shared use
> with other apps and related databases..
> install 2 separated instances, 1 of WMSDE to be used by SharePoint and
> another MSDE one to be used by your other apps.
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Jody,
Jody wrote:
> Andrea,
> In reference to you comment:
> "...it has been modified to fit SharePoint needs and you should avoid
> using it for your own databases..."
> Do you mean that I should avoid using WMSDE with WSS or along side of
> SmarterTicket becasue it uses MSDE?
I mean WMSDE should be used only by SharePoint and let SmarterTicket uses
another MSDE instance ..

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

Monday, March 12, 2012

MSDE - Start Databases

Hello

I'm using SQL Server 2000 - 8.00.760 Desktop Engine.

When the SQL Server starts, it just start databases master, model and tempdb. The databases that I have created don't start. They just start when I connect to them for the first time.

What I can do to start my own database when the SQL Server starts?

Thank you.

All databases are opened at startup if they are attached to SQl Server. If you have switched on the autoclose option, they are in common closed (but not stopped) after the last user disconnected.

jens Suessmeyer.

http://www.sqlserver2005.de

|||I did't switched on the autoclose option - it is off, but my database just start on first use, causing a delay on my application while it starts. When it is already started, I have no delay. I need start my database when SQLServer (MSDE) starts. Is there anything to do to start my database on start of SQLServer (MSDE)?|||

The Service is autostart, but my own databases just start when I connect to them to the first time.

Is there anything to do to auto start databases?

Thanks.

|||Are you sure you are using a MSDE database not a sqlserver express database. This sounds like you are using user instances which are only available after connecting the first time. Where do you actually administer the SQL server databases ? After starting the server do you see the entries in sysdatabase tables / views using SELECT * from sysdatabases ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

I am using MSDE database. To administer SQL Server I use OSQL.

After start the server I ran SELECT * from sysdatabases. My own databases are listed.

|||If the databases are listed they should be available. use the SELECT name, mode, status,status2 from sysdatabases to check their current status. What is currently displayed ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

I have three databases. They are listed and the databases master, model, msdb and tempdb are listed too.

After start SQLServer, the SELECT results the same values for my three databases: mode = 0, status = 1073741848 and status2 = 16777216.

Here, my own databases are not started. In the first connect to the database I has a delay, because they are not started.

Then, I connect to one and the value for status changes to 24. Now, it is started.

Any idea?

MSDE - Start Databases

Hello

I'm using SQL Server 2000 - 8.00.760 Desktop Engine.

When the SQL Server starts, it just start databases master, model and tempdb. The databases that I have created don't start. They just start when I connect to them for the first time.

What I can do to start my own database when the SQL Server starts?

Thank you.

All databases are opened at startup if they are attached to SQl Server. If you have switched on the autoclose option, they are in common closed (but not stopped) after the last user disconnected.

jens Suessmeyer.

http://www.sqlserver2005.de

The Service is autostart, but my own databases just start when I connect to them to the first time.

Is there anything to do to auto start databases?

Thanks.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I am using MSDE database. To administer SQL Server I use OSQL.

After start the server I ran SELECT * from sysdatabases. My own databases are listed.

|||If the databases are listed they should be available. use the SELECT name, mode, status,status2 from sysdatabases to check their current status. What is currently displayed ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I have three databases. They are listed and the databases master, model, msdb and tempdb are listed too.

After start SQLServer, the SELECT results the same values for my three databases: mode = 0, status = 1073741848 and status2 = 16777216.

Here, my own databases are not started. In the first connect to the database I has a delay, because they are not started.

Then, I connect to one and the value for status changes to 24. Now, it is started.

Any idea?

Friday, March 9, 2012

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

Saturday, February 25, 2012

msdb suspect

After move the whole sql server databases to a new box, msdb shows suspect,
the other databases all work fine.
A select statement shows that the staus of msdb is 280.
After I change the status to 24, it still does not work. I can't start
SQLServerAgent now.
Please help!!!
thanks, FeiCheck out the SQL Server errorlog why it became suspect. Perhaps you don't have the files for msdb
in the same directory as the original SQL Server, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Fei" <Fei@.discussions.microsoft.com> wrote in message
news:81056E87-078C-4E54-91EA-D2A7273B4554@.microsoft.com...
> After move the whole sql server databases to a new box, msdb shows suspect,
> the other databases all work fine.
> A select statement shows that the staus of msdb is 280.
> After I change the status to 24, it still does not work. I can't start
> SQLServerAgent now.
> Please help!!!
> thanks, Fei|||Tibor,
Thanks for your quick response.
I checked the errorlog and there is no related error msg found in the log.
The msdb files are on a different location (e:drive) from the original
installation (c: drive)because we wanted to seperate the datafiles from the
software files. The other system database files such as master, model are all
in the same location as the msdb and the other system databases are just
fine.
Any suggestions?
Fei
"Tibor Karaszi" wrote:
> Check out the SQL Server errorlog why it became suspect. Perhaps you don't have the files for msdb
> in the same directory as the original SQL Server, for instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Fei" <Fei@.discussions.microsoft.com> wrote in message
> news:81056E87-078C-4E54-91EA-D2A7273B4554@.microsoft.com...
> > After move the whole sql server databases to a new box, msdb shows suspect,
> > the other databases all work fine.
> >
> > A select statement shows that the staus of msdb is 280.
> >
> > After I change the status to 24, it still does not work. I can't start
> > SQLServerAgent now.
> >
> > Please help!!!
> >
> > thanks, Fei
>|||Since the path for msdb is in the master database, it is expected that msdb becomes suspect if you
move the files to some different location. I suggest you move the files back and then follow
instructions in either KB 224071 if on 2000 or Books Online of on 2005 on how to move files for
msdb.
Also, there *are* error messages for msdb to be found in the errorlog file, you just need to find
the right errorlog file (first startup when it became suspect).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Fei" <Fei@.discussions.microsoft.com> wrote in message
news:36CB5CBE-2373-4261-97C0-1AA615E564E4@.microsoft.com...
> Tibor,
> Thanks for your quick response.
> I checked the errorlog and there is no related error msg found in the log.
> The msdb files are on a different location (e:drive) from the original
> installation (c: drive)because we wanted to seperate the datafiles from the
> software files. The other system database files such as master, model are all
> in the same location as the msdb and the other system databases are just
> fine.
> Any suggestions?
>
> Fei
> "Tibor Karaszi" wrote:
>> Check out the SQL Server errorlog why it became suspect. Perhaps you don't have the files for
>> msdb
>> in the same directory as the original SQL Server, for instance.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Fei" <Fei@.discussions.microsoft.com> wrote in message
>> news:81056E87-078C-4E54-91EA-D2A7273B4554@.microsoft.com...
>> > After move the whole sql server databases to a new box, msdb shows suspect,
>> > the other databases all work fine.
>> >
>> > A select statement shows that the staus of msdb is 280.
>> >
>> > After I change the status to 24, it still does not work. I can't start
>> > SQLServerAgent now.
>> >
>> > Please help!!!
>> >
>> > thanks, Fei|||Hi Tibor
> Since the path for msdb is in the master database, it is expected that
> msdb becomes suspect if you move the files to some different location.
In KB 224071 they mention in how to move MSDB database the different
location to be moved the MSDB database
4. Move the Msdbdata.mdf and Msdblog.ldf files from the current
location (D:\Mssql8\Data) to the new location (E:\Mssql8\Data).
However the new master's path is dE:\SQLDATA\master.mdf
and -lE:\SQLDATA\mastlog.ldf. So should msdb database be attach to the same
path?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:C6840BE3-DB95-4C18-A44E-7630B5A67734@.microsoft.com...
> Since the path for msdb is in the master database, it is expected that
> msdb becomes suspect if you move the files to some different location. I
> suggest you move the files back and then follow instructions in either KB
> 224071 if on 2000 or Books Online of on 2005 on how to move files for
> msdb.
> Also, there *are* error messages for msdb to be found in the errorlog
> file, you just need to find the right errorlog file (first startup when it
> became suspect).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Fei" <Fei@.discussions.microsoft.com> wrote in message
> news:36CB5CBE-2373-4261-97C0-1AA615E564E4@.microsoft.com...
>> Tibor,
>> Thanks for your quick response.
>> I checked the errorlog and there is no related error msg found in the
>> log.
>> The msdb files are on a different location (e:drive) from the original
>> installation (c: drive)because we wanted to seperate the datafiles from
>> the
>> software files. The other system database files such as master, model are
>> all
>> in the same location as the msdb and the other system databases are just
>> fine.
>> Any suggestions?
>>
>> Fei
>> "Tibor Karaszi" wrote:
>> Check out the SQL Server errorlog why it became suspect. Perhaps you
>> don't have the files for msdb
>> in the same directory as the original SQL Server, for instance.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Fei" <Fei@.discussions.microsoft.com> wrote in message
>> news:81056E87-078C-4E54-91EA-D2A7273B4554@.microsoft.com...
>> > After move the whole sql server databases to a new box, msdb shows
>> > suspect,
>> > the other databases all work fine.
>> >
>> > A select statement shows that the staus of msdb is 280.
>> >
>> > After I change the status to 24, it still does not work. I can't
>> > start
>> > SQLServerAgent now.
>> >
>> > Please help!!!
>> >
>> > thanks, Fei
>|||No msdb doesn't have to be in the same path as master. But the path to msdb is *in* master, so you
can't just move the files to some other location (which is true for any database).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OhPtlBTvHHA.4540@.TK2MSFTNGP05.phx.gbl...
> Hi Tibor
>> Since the path for msdb is in the master database, it is expected that msdb becomes suspect if
>> you move the files to some different location.
> In KB 224071 they mention in how to move MSDB database the different location to be moved the
> MSDB database
> 4. Move the Msdbdata.mdf and Msdblog.ldf files from the current location (D:\Mssql8\Data) to
> the new location (E:\Mssql8\Data).
>
> However the new master's path is dE:\SQLDATA\master.mdf and -lE:\SQLDATA\mastlog.ldf. So should
> msdb database be attach to the same path?
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:C6840BE3-DB95-4C18-A44E-7630B5A67734@.microsoft.com...
>> Since the path for msdb is in the master database, it is expected that msdb becomes suspect if
>> you move the files to some different location. I suggest you move the files back and then follow
>> instructions in either KB 224071 if on 2000 or Books Online of on 2005 on how to move files for
>> msdb.
>> Also, there *are* error messages for msdb to be found in the errorlog file, you just need to find
>> the right errorlog file (first startup when it became suspect).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Fei" <Fei@.discussions.microsoft.com> wrote in message
>> news:36CB5CBE-2373-4261-97C0-1AA615E564E4@.microsoft.com...
>> Tibor,
>> Thanks for your quick response.
>> I checked the errorlog and there is no related error msg found in the log.
>> The msdb files are on a different location (e:drive) from the original
>> installation (c: drive)because we wanted to seperate the datafiles from the
>> software files. The other system database files such as master, model are all
>> in the same location as the msdb and the other system databases are just
>> fine.
>> Any suggestions?
>>
>> Fei
>> "Tibor Karaszi" wrote:
>> Check out the SQL Server errorlog why it became suspect. Perhaps you don't have the files for
>> msdb
>> in the same directory as the original SQL Server, for instance.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Fei" <Fei@.discussions.microsoft.com> wrote in message
>> news:81056E87-078C-4E54-91EA-D2A7273B4554@.microsoft.com...
>> > After move the whole sql server databases to a new box, msdb shows suspect,
>> > the other databases all work fine.
>> >
>> > A select statement shows that the staus of msdb is 280.
>> >
>> > After I change the status to 24, it still does not work. I can't start
>> > SQLServerAgent now.
>> >
>> > Please help!!!
>> >
>> > thanks, Fei
>>
>|||Tibor
Ah, yes. I just misunderstood your comments.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uh84O8ZvHHA.4540@.TK2MSFTNGP05.phx.gbl...
> No msdb doesn't have to be in the same path as master. But the path to
> msdb is *in* master, so you can't just move the files to some other
> location (which is true for any database).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OhPtlBTvHHA.4540@.TK2MSFTNGP05.phx.gbl...
>> Hi Tibor
>> Since the path for msdb is in the master database, it is expected that
>> msdb becomes suspect if you move the files to some different location.
>> In KB 224071 they mention in how to move MSDB database the different
>> location to be moved the MSDB database
>> 4. Move the Msdbdata.mdf and Msdblog.ldf files from the current
>> location (D:\Mssql8\Data) to the new location (E:\Mssql8\Data).
>>
>> However the new master's path is dE:\SQLDATA\master.mdf
>> and -lE:\SQLDATA\mastlog.ldf. So should msdb database be attach to the
>> same path?
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:C6840BE3-DB95-4C18-A44E-7630B5A67734@.microsoft.com...
>> Since the path for msdb is in the master database, it is expected that
>> msdb becomes suspect if you move the files to some different location. I
>> suggest you move the files back and then follow instructions in either
>> KB 224071 if on 2000 or Books Online of on 2005 on how to move files for
>> msdb.
>> Also, there *are* error messages for msdb to be found in the errorlog
>> file, you just need to find the right errorlog file (first startup when
>> it became suspect).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Fei" <Fei@.discussions.microsoft.com> wrote in message
>> news:36CB5CBE-2373-4261-97C0-1AA615E564E4@.microsoft.com...
>> Tibor,
>> Thanks for your quick response.
>> I checked the errorlog and there is no related error msg found in the
>> log.
>> The msdb files are on a different location (e:drive) from the original
>> installation (c: drive)because we wanted to seperate the datafiles from
>> the
>> software files. The other system database files such as master, model
>> are all
>> in the same location as the msdb and the other system databases are
>> just
>> fine.
>> Any suggestions?
>>
>> Fei
>> "Tibor Karaszi" wrote:
>> Check out the SQL Server errorlog why it became suspect. Perhaps you
>> don't have the files for msdb
>> in the same directory as the original SQL Server, for instance.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Fei" <Fei@.discussions.microsoft.com> wrote in message
>> news:81056E87-078C-4E54-91EA-D2A7273B4554@.microsoft.com...
>> > After move the whole sql server databases to a new box, msdb shows
>> > suspect,
>> > the other databases all work fine.
>> >
>> > A select statement shows that the staus of msdb is 280.
>> >
>> > After I change the status to 24, it still does not work. I can't
>> > start
>> > SQLServerAgent now.
>> >
>> > Please help!!!
>> >
>> > thanks, Fei
>>
>>
>

msdb refuses to back up w/ plan

All,
I've created (and re-created) a Mx plan for the system databases.
Part of it is a complete backup. All the default options in the
wizard were chosen, including the option to verify.
Master and Model back up fine. MSDB, however, fails. According to
the Mx Plan log, the backup completes successfully, but the verify
fails as follows:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backu
p
device 'd:\sql_data\MSSQL\BACKUP\msdb_db_200407
042207.BAK'. Device
error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE i
s
terminating abnormally.
1- It doesn't appear that the backup actually succeeded, as there is
no msdb_db_200407042207.bak file in the directory.
2- There is no additional information in the SQL Server error log
3- I can successfully back up msdb -manually- without a hiccup.
I've tried:
1- Recreating a new Mx Plan from scratch (no effect)
2- Can full index/optimizations on msdb (no errors found)
3- Tried shrinking the database (it's 215MBish) to make it faster
4- Tried a full backup/restore of msdb in case it had any mystery
corruption (no effect)
Oh yes, SQL Server standard 2000 sp3 on W2.3K standard. NTFS
partition. SQL Agent using a domain account, SQL Server running under
the same account.
Can anyone shed light on this? I can come up with no reason why the
Mx plan would fail for just this one database, when I can back it up
just fine manually.
GeofHere's some more info:
I did find some additional information in the app event log, giving the spec
ific T-SQL that was failing:
BACKUP DATABASE [msdb] TO DISK = N'd:\sql_data\MSSQL\BACKUP\msdb_db_200
407042238.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
After some pondering and re-reading my own post, I went ahead and logged int
o the server interactively using SQL Server's account. I attempted to execu
te the above SQL and lo and behond, it failed (General Network Error). When
executed under my normal U
serID, it executed fine.
Trial and error then produced the rather odd discovery that the problem part
of the statement is the seemingly innocuous "STATS=10" command. If the sam
e statement is run under the SQL Server account EXCEPT for removing the stat
s=10 command, it works fine|||Go the Maintenance Plan and right click and view maintenance history out
there ...Thats where most of the reasoning is for the failure..
"Geof" <Geof@.discussions.microsoft.com> wrote in message
news:C6436F94-BF81-41FD-9869-5595F7FBA6B4@.microsoft.com...
> Here's some more info:
> I did find some additional information in the app event log, giving the
specific T-SQL that was failing:
> BACKUP DATABASE [msdb] TO DISK =
N'd:\sql_data\MSSQL\BACKUP\msdb_db_20040
7042238.BAK' WITH INIT , NOUNLOAD
, NOSKIP , STATS = 10, NOFORMAT
> After some pondering and re-reading my own post, I went ahead and logged
into the server interactively using SQL Server's account. I attempted to
execute the above SQL and lo and behond, it failed (General Network Error).
When executed under my normal UserID, it executed fine.
> Trial and error then produced the rather odd discovery that the problem
part of the statement is the seemingly innocuous "STATS=10" command. If the
same statement is run under the SQL Server account EXCEPT for removing the
stats=10 command, it works fine.
> While I've gotten more information on exactly what is causing the problem,
I:
> 1- Have no idea why the problem is occurring, and
> 2- Don't know a way around it (other than backup w/o using a Mx Plan)
> My nearest stab would be that this particular T-SQL (and STATS option in
particular) is causing some sort of collision between SQL Server and SQL
Server Agent since they would both be running under the same account. (But
surely I'm not the only one in the history of SQL Server to have created a
sysdb mx plan w/ both SQL and SQL Agent running under an identical domain
account, huh')
> Ideas'|||In my first post I listed what was in the Mx Plan History. Does
anyone have any other ideas?
Geof
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:<#GDGyveYEHA.996@.TK2MSFTNGP12.phx.gbl
>...
> Go the Maintenance Plan and right click and view maintenance history out
> there ...Thats where most of the reasoning is for the failure..

msdb refuses to back up w/ plan

All,
I've created (and re-created) a Mx plan for the system databases.
Part of it is a complete backup. All the default options in the
wizard were chosen, including the option to verify.
Master and Model back up fine. MSDB, however, fails. According to
the Mx Plan log, the backup completes successfully, but the verify
fails as follows:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup
device 'd:\sql_data\MSSQL\BACKUP\msdb_db_200407042207.BAK '. Device
error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
1- It doesn't appear that the backup actually succeeded, as there is
no msdb_db_200407042207.bak file in the directory.
2- There is no additional information in the SQL Server error log
3- I can successfully back up msdb -manually- without a hiccup.
I've tried:
1- Recreating a new Mx Plan from scratch (no effect)
2- Can full index/optimizations on msdb (no errors found)
3- Tried shrinking the database (it's 215MBish) to make it faster
4- Tried a full backup/restore of msdb in case it had any mystery
corruption (no effect)
Oh yes, SQL Server standard 2000 sp3 on W2.3K standard. NTFS
partition. SQL Agent using a domain account, SQL Server running under
the same account.
Can anyone shed light on this? I can come up with no reason why the
Mx plan would fail for just this one database, when I can back it up
just fine manually.
Geof
Here's some more info:
I did find some additional information in the app event log, giving the specific T-SQL that was failing:
BACKUP DATABASE [msdb] TO DISK = N'd:\sql_data\MSSQL\BACKUP\msdb_db_200407042238.BA K' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
After some pondering and re-reading my own post, I went ahead and logged into the server interactively using SQL Server's account. I attempted to execute the above SQL and lo and behond, it failed (General Network Error). When executed under my normal U
serID, it executed fine.
Trial and error then produced the rather odd discovery that the problem part of the statement is the seemingly innocuous "STATS=10" command. If the same statement is run under the SQL Server account EXCEPT for removing the stats=10 command, it works fine
|||Go the Maintenance Plan and right click and view maintenance history out
there ...Thats where most of the reasoning is for the failure..
"Geof" <Geof@.discussions.microsoft.com> wrote in message
news:C6436F94-BF81-41FD-9869-5595F7FBA6B4@.microsoft.com...
> Here's some more info:
> I did find some additional information in the app event log, giving the
specific T-SQL that was failing:
> BACKUP DATABASE [msdb] TO DISK =
N'd:\sql_data\MSSQL\BACKUP\msdb_db_200407042238.BA K' WITH INIT , NOUNLOAD
, NOSKIP , STATS = 10, NOFORMAT
> After some pondering and re-reading my own post, I went ahead and logged
into the server interactively using SQL Server's account. I attempted to
execute the above SQL and lo and behond, it failed (General Network Error).
When executed under my normal UserID, it executed fine.
> Trial and error then produced the rather odd discovery that the problem
part of the statement is the seemingly innocuous "STATS=10" command. If the
same statement is run under the SQL Server account EXCEPT for removing the
stats=10 command, it works fine.
> While I've gotten more information on exactly what is causing the problem,
I:
> 1- Have no idea why the problem is occurring, and
> 2- Don't know a way around it (other than backup w/o using a Mx Plan)
> My nearest stab would be that this particular T-SQL (and STATS option in
particular) is causing some sort of collision between SQL Server and SQL
Server Agent since they would both be running under the same account. (But
surely I'm not the only one in the history of SQL Server to have created a
sysdb mx plan w/ both SQL and SQL Agent running under an identical domain
account, huh?)
> Ideas?
|||In my first post I listed what was in the Mx Plan History. Does
anyone have any other ideas?
Geof
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:<#GDGyveYEHA.996@.TK2MSFTNGP12.phx.gbl>...
> Go the Maintenance Plan and right click and view maintenance history out
> there ...Thats where most of the reasoning is for the failure..

msdb refuses to back up w/ plan

All,
I've created (and re-created) a Mx plan for the system databases.
Part of it is a complete backup. All the default options in the
wizard were chosen, including the option to verify.
Master and Model back up fine. MSDB, however, fails. According to
the Mx Plan log, the backup completes successfully, but the verify
fails as follows:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup
device 'd:\sql_data\MSSQL\BACKUP\msdb_db_200407042207.BAK'. Device
error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
1- It doesn't appear that the backup actually succeeded, as there is
no msdb_db_200407042207.bak file in the directory.
2- There is no additional information in the SQL Server error log
3- I can successfully back up msdb -manually- without a hiccup.
I've tried:
1- Recreating a new Mx Plan from scratch (no effect)
2- Can full index/optimizations on msdb (no errors found)
3- Tried shrinking the database (it's 215MBish) to make it faster
4- Tried a full backup/restore of msdb in case it had any mystery
corruption (no effect)
Oh yes, SQL Server standard 2000 sp3 on W2.3K standard. NTFS
partition. SQL Agent using a domain account, SQL Server running under
the same account.
Can anyone shed light on this? I can come up with no reason why the
Mx plan would fail for just this one database, when I can back it up
just fine manually.
GeofHere's some more info:
I did find some additional information in the app event log, giving the specific T-SQL that was failing:
BACKUP DATABASE [msdb] TO DISK = N'd:\sql_data\MSSQL\BACKUP\msdb_db_200407042238.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
After some pondering and re-reading my own post, I went ahead and logged into the server interactively using SQL Server's account. I attempted to execute the above SQL and lo and behond, it failed (General Network Error). When executed under my normal UserID, it executed fine.
Trial and error then produced the rather odd discovery that the problem part of the statement is the seemingly innocuous "STATS=10" command. If the same statement is run under the SQL Server account EXCEPT for removing the stats=10 command, it works fine.
While I've gotten more information on exactly what is causing the problem, I:
1- Have no idea why the problem is occurring, and
2- Don't know a way around it (other than backup w/o using a Mx Plan)
My nearest stab would be that this particular T-SQL (and STATS option in particular) is causing some sort of collision between SQL Server and SQL Server Agent since they would both be running under the same account. (But surely I'm not the only one in the history of SQL Server to have created a sysdb mx plan w/ both SQL and SQL Agent running under an identical domain account, huh')
Ideas'|||Go the Maintenance Plan and right click and view maintenance history out
there ...Thats where most of the reasoning is for the failure..
"Geof" <Geof@.discussions.microsoft.com> wrote in message
news:C6436F94-BF81-41FD-9869-5595F7FBA6B4@.microsoft.com...
> Here's some more info:
> I did find some additional information in the app event log, giving the
specific T-SQL that was failing:
> BACKUP DATABASE [msdb] TO DISK =N'd:\sql_data\MSSQL\BACKUP\msdb_db_200407042238.BAK' WITH INIT , NOUNLOAD
, NOSKIP , STATS = 10, NOFORMAT
> After some pondering and re-reading my own post, I went ahead and logged
into the server interactively using SQL Server's account. I attempted to
execute the above SQL and lo and behond, it failed (General Network Error).
When executed under my normal UserID, it executed fine.
> Trial and error then produced the rather odd discovery that the problem
part of the statement is the seemingly innocuous "STATS=10" command. If the
same statement is run under the SQL Server account EXCEPT for removing the
stats=10 command, it works fine.
> While I've gotten more information on exactly what is causing the problem,
I:
> 1- Have no idea why the problem is occurring, and
> 2- Don't know a way around it (other than backup w/o using a Mx Plan)
> My nearest stab would be that this particular T-SQL (and STATS option in
particular) is causing some sort of collision between SQL Server and SQL
Server Agent since they would both be running under the same account. (But
surely I'm not the only one in the history of SQL Server to have created a
sysdb mx plan w/ both SQL and SQL Agent running under an identical domain
account, huh')
> Ideas'|||In my first post I listed what was in the Mx Plan History. Does
anyone have any other ideas?
Geof
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:<#GDGyveYEHA.996@.TK2MSFTNGP12.phx.gbl>...
> Go the Maintenance Plan and right click and view maintenance history out
> there ...Thats where most of the reasoning is for the failure..

Monday, February 20, 2012

msdb error

When I try to open the Tuning Advisor or when I try to view the property
of the system databases or when I try to start the Server Agent I receive
the following error:
Database 'msdb' cannot be opened due to inaccessible files or insufficient
memory or disk space. See the SQL Server errorlog for details. (Microsoft
SQL Server, Error: 945)
Note: I can see the msdb database listed under the System Databases node
but without the plus [+] sign.
How can I rebuild the msdb database (I don't have a backup of course)?
Thanks in advance,
AlessandroIs the disk available? Disk full? Is there any additional information on the
SQL Server error log file (as recommended by the error message)?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alessandro Cavalieri" wrote:

> When I try to open the Tuning Advisor or when I try to view the property
> of the system databases or when I try to start the Server Agent I receive
> the following error:
> Database 'msdb' cannot be opened due to inaccessible files or insufficient
> memory or disk space. See the SQL Server errorlog for details. (Microsoft
> SQL Server, Error: 945)
> Note: I can see the msdb database listed under the System Databases node
> but without the plus [+] sign.
> How can I rebuild the msdb database (I don't have a backup of course)?
> Thanks in advance,
> Alessandro
>
>|||Hello Ben,
Yes the disk file is available and not full, and the data file (mdf) and
the log file (ldf) are healty (everything is on drive C).
> Is the disk available? Disk full? Is there any additional information
> on the SQL Server error log file (as recommended by the error
> message)?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
> "Alessandro Cavalieri" wrote:
>

Monday, March 26, 2012

MSDE 2000 problems with linked server

MSDE 2000 problems with linked server

MSDE 2000 problems with linked server

Friday, March 23, 2012

MSDE 2000 database setup trouble

MSDE 2000 connectivity problem

Monday, March 19, 2012

MSDE / SQL Server

MSDE / SQL Server

MSDE & WMSDE

Monday, March 12, 2012

MSDE - Start Databases

MSDE - Start Databases

Friday, March 9, 2012

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

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

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

Saturday, February 25, 2012

msdb suspect

msdb suspect

msdb suspect

msdb refuses to back up w/ plan

msdb refuses to back up w/ plan

msdb refuses to back up w/ plan

Monday, February 20, 2012

msdb error

MSDB database restore

Blog Archive

About Me