"Timeout expired" errors

User is getting timeout errors when they try to backup a 2000 database using
a db backup and restore utility I wrote. The problem has just started
occurring w/in the last few days. I'd like to avoid driving to the site,
over 200-miles away, and changing the .ConnectionTimeout and .CommandTimeout
property of the db connection object building another distribution. Is there
a way to accomplish thisi via some "SET" command in Enterprise Mgr.?
TIATimeouts occur on the client side, not on the server, so there is no EM
setting. I hope you enjoy your trip :-)
pe this helps.
Dan Guzman
SQL Server MVP
"PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
> User is getting timeout errors when they try to backup a 2000 database
> using a db backup and restore utility I wrote. The problem has just
> started occurring w/in the last few days. I'd like to avoid driving to the
> site, over 200-miles away, and changing the .ConnectionTimeout and
> .CommandTimeout property of the db connection object building another
> distribution. Is there a way to accomplish thisi via some "SET" command in
> Enterprise Mgr.?
> TIA
>
>|||thanks ;o(
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eBzkJGIxFHA.2252@.TK2MSFTNGP09.phx.gbl...
> Timeouts occur on the client side, not on the server, so there is no EM
> setting. I hope you enjoy your trip :-)
>
> pe this helps.
> Dan Guzman
> SQL Server MVP
> "PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
> news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
>> User is getting timeout errors when they try to backup a 2000 database
>> using a db backup and restore utility I wrote. The problem has just
>> started occurring w/in the last few days. I'd like to avoid driving to
>> the site, over 200-miles away, and changing the .ConnectionTimeout and
>> .CommandTimeout property of the db connection object building another
>> distribution. Is there a way to accomplish thisi via some "SET" command
>> in Enterprise Mgr.?
>> TIA
>>
>

"Timeout expired" errors

User is getting timeout errors when they try to backup a 2000 database using
a db backup and restore utility I wrote. The problem has just started
occurring w/in the last few days. I'd like to avoid driving to the site,
over 200-miles away, and changing the .ConnectionTimeout and .CommandTimeout
property of the db connection object building another distribution. Is there
a way to accomplish thisi via some "SET" command in Enterprise Mgr.?
TIATimeouts occur on the client side, not on the server, so there is no EM
setting. I hope you enjoy your trip :-)
pe this helps.
Dan Guzman
SQL Server MVP
"PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
> User is getting timeout errors when they try to backup a 2000 database
> using a db backup and restore utility I wrote. The problem has just
> started occurring w/in the last few days. I'd like to avoid driving to the
> site, over 200-miles away, and changing the .ConnectionTimeout and
> .CommandTimeout property of the db connection object building another
> distribution. Is there a way to accomplish thisi via some "SET" command in
> Enterprise Mgr.?
> TIA
>
>|||thanks ;o(
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eBzkJGIxFHA.2252@.TK2MSFTNGP09.phx.gbl...
> Timeouts occur on the client side, not on the server, so there is no EM
> setting. I hope you enjoy your trip :-)
>
> pe this helps.
> Dan Guzman
> SQL Server MVP
> "PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
> news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
>

"Timeout expired" errors

User is getting timeout errors when they try to backup a 2000 database using
a db backup and restore utility I wrote. The problem has just started
occurring w/in the last few days. I'd like to avoid driving to the site,
over 200-miles away, and changing the .ConnectionTimeout and .CommandTimeout
property of the db connection object building another distribution. Is there
a way to accomplish thisi via some "SET" command in Enterprise Mgr.?
TIA
Timeouts occur on the client side, not on the server, so there is no EM
setting. I hope you enjoy your trip :-)
pe this helps.
Dan Guzman
SQL Server MVP
"PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
> User is getting timeout errors when they try to backup a 2000 database
> using a db backup and restore utility I wrote. The problem has just
> started occurring w/in the last few days. I'd like to avoid driving to the
> site, over 200-miles away, and changing the .ConnectionTimeout and
> .CommandTimeout property of the db connection object building another
> distribution. Is there a way to accomplish thisi via some "SET" command in
> Enterprise Mgr.?
> TIA
>
>
|||thanks ;o(
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eBzkJGIxFHA.2252@.TK2MSFTNGP09.phx.gbl...
> Timeouts occur on the client side, not on the server, so there is no EM
> setting. I hope you enjoy your trip :-)
>
> pe this helps.
> Dan Guzman
> SQL Server MVP
> "PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
> news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
>

"Restore Database" when unable to "Connect"?

Hey

Quick question for the experts here! Wink

We have the issue of wanting to restore an MSSQL database (.BAK) but are unable to "Connect" to the existing database; but we are able to "Start" the database currently.

When we are unable to perform a "Connect", then under "All Tasks" you don't get the option to "Restore Database"

I am hoping there is a way around this and still a possibility of doing a restore without having to do an entire install of MSSQL again and then a restore?

Thanks!When you say you are unable to "connect" do you mean with the application, or with the SQL Server tools?

Also, when you say "start the database" do you mean the MSSQLServer service?

Sorry for the questions, I just want to make sure we are talking the same language.|||Please disregard, seems that there was a registration issue with admin rights, so I have since connected as Administrator and am abale to do a "Restore Database" again.

Thanks a lot

"Identical" database, huge performance difference

I have a database that has big performance issue. I backup (complete
backup) the database and restore it with a new name within the same
instance, the performance gets back to normal. The size of both database
is 1,900 MB and space available of both is 1,100 MB.
Any idea about what makes the difference?so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with DB1 have a serious
fragmentation problem?
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"qluo" <jluost1@.yahoo.com> wrote in message
news:40042824.2030705@.yahoo.com...
> I have a database that has big performance issue. I backup (complete
> backup) the database and restore it with a new name within the same
> instance, the performance gets back to normal. The size of both database
> is 1,900 MB and space available of both is 1,100 MB.
> Any idea about what makes the difference?
>|||Brian,
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes 36ms consistently.
I have not looked at the fragmentation problem yet. But the data file
and log file for both databases are in the same physical location.
Brian Moran wrote:
> so you're saying that you can:
> * start with DB1
> * back up DB1 and restore it as DB2
> * you could then run the same exact query on DB1 and DB2. It would be fast
> on DB2 and slow on DB1?
> * If so... what steps do you need to do to then make DB2 slow? Or does
> performance stay good forever?
> Have you looked at SQL Profiler?
> What are the exact performance problems you're seeing?
> Are you restoring DB2 to the same disk arrays as DB1?
> Is it possible that the physical files associated with DB1 have a serious
> fragmentation problem?
>|||is DB1 a live database? ie, are people connecting to DB1
and making updates to table1?
if so, then the reason DB2 is faster for the same query is
that it knows no one has updated table1 on DB2, hence it
is safe to execute as select count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent
inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
>--Original Message--
>Brian,
>You are correct for the procedures I performed.
>The performance problem is, the Web page with DB1 as the
backend
>database takes too long to load.
>The query I used in Query Analyzer on both DB1 and DB2 is:
>--
>select count(*) from table1
>--
>It returns 72,000 on both database.
>For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
>I have not looked at the fragmentation problem yet. But
the data file
>and log file for both databases are in the same physical
location.
>
>Brian Moran wrote:
>> so you're saying that you can:
>> * start with DB1
>> * back up DB1 and restore it as DB2
>> * you could then run the same exact query on DB1 and
DB2. It would be fast
>> on DB2 and slow on DB1?
>> * If so... what steps do you need to do to then make
DB2 slow? Or does
>> performance stay good forever?
>> Have you looked at SQL Profiler?
>> What are the exact performance problems you're seeing?
>> Are you restoring DB2 to the same disk arrays as DB1?
>> Is it possible that the physical files associated with
DB1 have a serious
>> fragmentation problem?
>.
>|||Your point is exactly right. I ran the query with (NOLOCK) on both DB1
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
> is DB1 a live database? ie, are people connecting to DB1
> and making updates to table1?
> if so, then the reason DB2 is faster for the same query is
> that it knows no one has updated table1 on DB2, hence it
> is safe to execute as select count(*) from table1 (NOLOCK)
> while DB1 must row lock if there were recent
> inserts/upd/del to table1
> try
> select count(*) from table1 (NOLOCK)
> on both
> -joe
>>--Original Message--
>>Brian,
>>You are correct for the procedures I performed.
>>The performance problem is, the Web page with DB1 as the
> backend
>>database takes too long to load.
>>The query I used in Query Analyzer on both DB1 and DB2 is:
>>--
>>select count(*) from table1
>>--
>>It returns 72,000 on both database.
>>For DB1, it takes 220 ms consistently; for DB2, it takes
> 36ms consistently.
>>I have not looked at the fragmentation problem yet. But
> the data file
>>and log file for both databases are in the same physical
> location.
>>Brian Moran wrote:
>>so you're saying that you can:
>>* start with DB1
>>* back up DB1 and restore it as DB2
>>* you could then run the same exact query on DB1 and
> DB2. It would be fast
>>on DB2 and slow on DB1?
>>* If so... what steps do you need to do to then make
> DB2 slow? Or does
>>performance stay good forever?
>>Have you looked at SQL Profiler?
>>What are the exact performance problems you're seeing?
>>Are you restoring DB2 to the same disk arrays as DB1?
>>Is it possible that the physical files associated with
> DB1 have a serious
>>fragmentation problem?
>>
>>.
>|||You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
connection. There should be a trx isolation level property for your
database connectivity object. Not that this setting will be effective for
all queries passing through the connection.
--
Regards
Ray Mond|||I am reluctant to change the default (Read Committed).
My database is not busy at all. It is used by Web app and every
connection should come and go almost immediately. There shouldn't be a
concurrent(locking) issue like this.
Do you know what else I can do to minimize the locking so that I don't
have to change TRANSACTION ISOLATION LEVEL?
Thank you.
Ray Mond wrote:
> You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
> connection. There should be a trx isolation level property for your
> database connectivity object. Not that this setting will be effective for
> all queries passing through the connection.
Your point is exactly right. I ran the query with (NOLOCK) on both DB1
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
is DB1 a live database? ie, are people connecting to DB1 and making
updates to table1?
if so, then the reason DB2 is faster for the same query is that it knows
no one has updated table1 on DB2, hence it is safe to execute as select
count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
--Original Message--
Brian,
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the
backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
I have not looked at the fragmentation problem yet. But
the data file
and log file for both databases are in the same physical
location.
Brian Moran wrote:
so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and
DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make
DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with
DB1 have a serious
fragmentation problem?
.
>|||I think the only option left here is to use that suggested by Joe Young i.e.
use the (NOLOCK) hint in the queries where you do not care about open
transactions.
--
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:400554D8.8080900@.yahoo.com...
> I am reluctant to change the default (Read Committed).
> My database is not busy at all. It is used by Web app and every
> connection should come and go almost immediately. There shouldn't be a
> concurrent(locking) issue like this.
> Do you know what else I can do to minimize the locking so that I don't
> have to change TRANSACTION ISOLATION LEVEL?
> Thank you.
> Ray Mond wrote:
> > You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
> > connection. There should be a trx isolation level property for your
> > database connectivity object. Not that this setting will be effective
for
> > all queries passing through the connection.
> Your point is exactly right. I ran the query with (NOLOCK) on both DB1
> and DB2 and they are taking the same amount of time, and dB1 is quicker
> now than before.
> So, what do I need to do on DB1 to achieve the same performance without
> using (NOLOCK)?
> DB1 is not actually busy and it is used by Web applications.
> Thank you.
>
> joe chang wrote:
> is DB1 a live database? ie, are people connecting to DB1 and making
> updates to table1?
> if so, then the reason DB2 is faster for the same query is that it knows
> no one has updated table1 on DB2, hence it is safe to execute as select
> count(*) from table1 (NOLOCK)
> while DB1 must row lock if there were recent inserts/upd/del to table1
> try
> select count(*) from table1 (NOLOCK)
> on both
> -joe
> --Original Message--
> Brian,
> You are correct for the procedures I performed.
> The performance problem is, the Web page with DB1 as the
> backend
> database takes too long to load.
> The query I used in Query Analyzer on both DB1 and DB2 is:
> --
> select count(*) from table1
> --
> It returns 72,000 on both database.
> For DB1, it takes 220 ms consistently; for DB2, it takes
> 36ms consistently.
> I have not looked at the fragmentation problem yet. But
> the data file
> and log file for both databases are in the same physical
> location.
>
> Brian Moran wrote:
> so you're saying that you can:
> * start with DB1
> * back up DB1 and restore it as DB2
> * you could then run the same exact query on DB1 and
> DB2. It would be fast
> on DB2 and slow on DB1?
> * If so... what steps do you need to do to then make
> DB2 slow? Or does
> performance stay good forever?
> Have you looked at SQL Profiler?
> What are the exact performance problems you're seeing?
> Are you restoring DB2 to the same disk arrays as DB1?
> Is it possible that the physical files associated with
> DB1 have a serious
> fragmentation problem?
>
> .
>
> >
>|||I did "Set transaction isolation level read uncommitted" on the database
server, but it made no difference. I think the OLEDB used by PHP Web app
is using "Set transaction isolation level read committed". So no
matter what I have set on database server doesn't matter.
I can use "NOLOCK" hint for each sql statement in the application. But
there are too many of them and I am reluctant to do so.
Again, I backed up DB1 and restore it with the new name DB2. I don't
need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
gain the same performance.
When I check the Process/Locks in SQL Enterprise Manager, I can see that
the locks come and go. When I see no locks for DB1 and then run my
sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
will make the difference for DB1 (it cuts the response time from 1.1
seconds to 0.6 seconds for 746 rows)?
Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
appear to be busy, indeed, it is not busy at all?
Thank you.
Ray Mond wrote:
> I think the only option left here is to use that suggested by Joe Young i.e.
> use the (NOLOCK) hint in the queries where you do not care about open
> transactions.
>|||By any chance, is DB2 (your database, not IBM's :) in read-only mode? Also,
could you pls run SET STATISTICS IO ON in Query Analyzer, run both queries
and post the resulting messages? Then run SET STATISTICS IO OFF and run SET
STATISTICS TIME ON, rerun the queries and post the messages too? Just
curious to see the results.
Thanks.
--
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:4006BF8E.7060302@.yahoo.com...
> I did "Set transaction isolation level read uncommitted" on the database
> server, but it made no difference. I think the OLEDB used by PHP Web app
> is using "Set transaction isolation level read committed". So no
> matter what I have set on database server doesn't matter.
> I can use "NOLOCK" hint for each sql statement in the application. But
> there are too many of them and I am reluctant to do so.
>
> Again, I backed up DB1 and restore it with the new name DB2. I don't
> need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
> gain the same performance.
> When I check the Process/Locks in SQL Enterprise Manager, I can see that >
the locks come and go. When I see no locks for DB1 and then run my
> sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
> will make the difference for DB1 (it cuts the response time from 1.1
> seconds to 0.6 seconds for 746 rows)?
> Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
> appear to be busy, indeed, it is not busy at all?
> Thank you.
>
>
> Ray Mond wrote:
> > I think the only option left here is to use that suggested by Joe Young
i.e.
> > use the (NOLOCK) hint in the queries where you do not care about open
> > transactions.
> >
>

"Identical" database, huge performance difference

I have a database that has big performance issue. I backup (complete
backup) the database and restore it with a new name within the same
instance, the performance gets back to normal. The size of both database
is 1,900 MB and space available of both is 1,100 MB.
Any idea about what makes the difference?so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with DB1 have a serious
fragmentation problem?
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"qluo" <jluost1@.yahoo.com> wrote in message
news:40042824.2030705@.yahoo.com...

quote:

---

> I have a database that has big performance issue. I backup (complete
> backup) the database and restore it with a new name within the same
> instance, the performance gets back to normal. The size of both database
> is 1,900 MB and space available of both is 1,100 MB.
> Any idea about what makes the difference?
>

---

|||Brian,
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes 36ms consistently.
I have not looked at the fragmentation problem yet. But the data file
and log file for both databases are in the same physical location.
Brian Moran wrote:

quote:

---

> so you're saying that you can:
> * start with DB1
> * back up DB1 and restore it as DB2
> * you could then run the same exact query on DB1 and DB2. It would be fast
> on DB2 and slow on DB1?
> * If so... what steps do you need to do to then make DB2 slow? Or does
> performance stay good forever?
> Have you looked at SQL Profiler?
> What are the exact performance problems you're seeing?
> Are you restoring DB2 to the same disk arrays as DB1?
> Is it possible that the physical files associated with DB1 have a serious
> fragmentation problem?
>

---

|||is DB1 a live database? ie, are people connecting to DB1
and making updates to table1?
if so, then the reason DB2 is faster for the same query is
that it knows no one has updated table1 on DB2, hence it
is safe to execute as select count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent
inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe

quote:

---

>--Original Message--
>Brian,
>You are correct for the procedures I performed.
>The performance problem is, the Web page with DB1 as the

---

backend

quote:

---

>database takes too long to load.
>The query I used in Query Analyzer on both DB1 and DB2 is:
>--
>select count(*) from table1
>--
>It returns 72,000 on both database.
>For DB1, it takes 220 ms consistently; for DB2, it takes

---

36ms consistently.

quote:

---

>I have not looked at the fragmentation problem yet. But

---

the data file

quote:

---

>and log file for both databases are in the same physical

---

location.

quote:

---

>
>Brian Moran wrote:
DB2. It would be fast[QUOTE]
DB2 slow? Or does[QUOTE]
DB1 have a serious[QUOTE]
>.
>

---

|||Your point is exactly right. I ran the query with (NOLOCK) on both DB1
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:

quote:

---

> is DB1 a live database? ie, are people connecting to DB1
> and making updates to table1?
> if so, then the reason DB2 is faster for the same query is
> that it knows no one has updated table1 on DB2, hence it
> is safe to execute as select count(*) from table1 (NOLOCK)
> while DB1 must row lock if there were recent
> inserts/upd/del to table1
> try
> select count(*) from table1 (NOLOCK)
> on both
> -joe
>
> backend
>
> 36ms consistently.
>
> the data file
>
> location.
>
> DB2. It would be fast
>
> DB2 slow? Or does
>
> DB1 have a serious
>
>

---

|||You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
connection. There should be a trx isolation level property for your
database connectivity object. Not that this setting will be effective for
all queries passing through the connection.
Regards
Ray Mond|||I am reluctant to change the default (Read Committed).
My database is not busy at all. It is used by Web app and every
connection should come and go almost immediately. There shouldn't be a
concurrent(locking) issue like this.
Do you know what else I can do to minimize the locking so that I don't
have to change TRANSACTION ISOLATION LEVEL?
Thank you.
Ray Mond wrote:

quote:

---

> You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
> connection. There should be a trx isolation level property for your
> database connectivity object. Not that this setting will be effective for
> all queries passing through the connection.

---

Your point is exactly right. I ran the query with (NOLOCK) on both DB1
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
is DB1 a live database? ie, are people connecting to DB1 and making
updates to table1?
if so, then the reason DB2 is faster for the same query is that it knows
no one has updated table1 on DB2, hence it is safe to execute as select
count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
--Original Message--
Brian,
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the
backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
I have not looked at the fragmentation problem yet. But
the data file
and log file for both databases are in the same physical
location.
Brian Moran wrote:
so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and
DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make
DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with
DB1 have a serious
fragmentation problem?
.

quote:

---

>

---

|||I think the only option left here is to use that suggested by Joe Young i.e.
use the (NOLOCK) hint in the queries where you do not care about open
transactions.
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:400554D8.8080900@.yahoo.com...

quote:

---

> I am reluctant to change the default (Read Committed).
> My database is not busy at all. It is used by Web app and every
> connection should come and go almost immediately. There shouldn't be a
> concurrent(locking) issue like this.
> Do you know what else I can do to minimize the locking so that I don't
> have to change TRANSACTION ISOLATION LEVEL?
> Thank you.
> Ray Mond wrote:
for[QUOTE]
> Your point is exactly right. I ran the query with (NOLOCK) on both DB1
> and DB2 and they are taking the same amount of time, and dB1 is quicker
> now than before.
> So, what do I need to do on DB1 to achieve the same performance without
> using (NOLOCK)?
> DB1 is not actually busy and it is used by Web applications.
> Thank you.
>
> joe chang wrote:
> is DB1 a live database? ie, are people connecting to DB1 and making
> updates to table1?
> if so, then the reason DB2 is faster for the same query is that it knows
> no one has updated table1 on DB2, hence it is safe to execute as select
> count(*) from table1 (NOLOCK)
> while DB1 must row lock if there were recent inserts/upd/del to table1
> try
> select count(*) from table1 (NOLOCK)
> on both
> -joe
> --Original Message--
> Brian,
> You are correct for the procedures I performed.
> The performance problem is, the Web page with DB1 as the
> backend
> database takes too long to load.
> The query I used in Query Analyzer on both DB1 and DB2 is:
> --
> select count(*) from table1
> --
> It returns 72,000 on both database.
> For DB1, it takes 220 ms consistently; for DB2, it takes
> 36ms consistently.
> I have not looked at the fragmentation problem yet. But
> the data file
> and log file for both databases are in the same physical
> location.
>
> Brian Moran wrote:
> so you're saying that you can:
> * start with DB1
> * back up DB1 and restore it as DB2
> * you could then run the same exact query on DB1 and
> DB2. It would be fast
> on DB2 and slow on DB1?
> * If so... what steps do you need to do to then make
> DB2 slow? Or does
> performance stay good forever?
> Have you looked at SQL Profiler?
> What are the exact performance problems you're seeing?
> Are you restoring DB2 to the same disk arrays as DB1?
> Is it possible that the physical files associated with
> DB1 have a serious
> fragmentation problem?
>
> .
>
>
>

---

|||I did "Set transaction isolation level read uncommitted" on the database
server, but it made no difference. I think the OLEDB used by php Web app
is using "Set transaction isolation level read committed". So no
matter what I have set on database server doesn't matter.
I can use "NOLOCK" hint for each sql statement in the application. But
there are too many of them and I am reluctant to do so.
Again, I backed up DB1 and restore it with the new name DB2. I don't
need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
gain the same performance.
When I check the Process/Locks in SQL Enterprise Manager, I can see that
the locks come and go. When I see no locks for DB1 and then run my
sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
will make the difference for DB1 (it cuts the response time from 1.1
seconds to 0.6 seconds for 746 rows)?
Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
appear to be busy, indeed, it is not busy at all?
Thank you.
Ray Mond wrote:

quote:

---

> I think the only option left here is to use that suggested by Joe Young i.
e.
> use the (NOLOCK) hint in the queries where you do not care about open
> transactions.
>

---

|||By any chance, is DB2 (your database, not IBM's in read-only mode? Also,
could you pls run SET STATISTICS IO ON in Query Analyzer, run both queries
and post the resulting messages? Then run SET STATISTICS IO OFF and run SET
STATISTICS TIME ON, rerun the queries and post the messages too? Just
curious to see the results.
Thanks.
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:4006BF8E.7060302@.yahoo.com...

quote:

---

> I did "Set transaction isolation level read uncommitted" on the database
> server, but it made no difference. I think the OLEDB used by php Web app
> is using "Set transaction isolation level read committed". So no
> matter what I have set on database server doesn't matter.
> I can use "NOLOCK" hint for each sql statement in the application. But
> there are too many of them and I am reluctant to do so.
>

---

quote:

---

> Again, I backed up DB1 and restore it with the new name DB2. I don't
> need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
> gain the same performance.
> When I check the Process/Locks in SQL Enterprise Manager, I can see that >

---

the locks come and go. When I see no locks for DB1 and then run my

quote:

---

> sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
> will make the difference for DB1 (it cuts the response time from 1.1
> seconds to 0.6 seconds for 746 rows)?
> Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
> appear to be busy, indeed, it is not busy at all?
> Thank you.
>
>
> Ray Mond wrote:
i.e.[QUOTE]
>

---

"exclusive access could not be obtained.." while restoring

Hello
I am using MSDE with my application and providing our users UI to backup/restore the database. My app has just 1 database and 1 login mapped to 1 user (MyAppUser). Backup and restore functionality is using inline sql commands. Backup works fine with something like this
Private Sub Backup(
Dim cn As New SqlConnection(MyConnectionString
Tr
cn.Open(
Dim cm As New SqlComman
With c
.Connection = c
.CommandType = CommandType.Tex
.CommandText = "BACKUP DATABASE MyDB TO DISK = 'D:\Backup\a.bak' WITH INIT
.ExecuteNonQuery(
End Wit
MsgBox("Database backed up successfully!"
Catch ex As Exceptio
MsgBox(ex.Message
Finall
cn.Close(
End Tr
End Su
but when I do restore using something like this
Private Sub Restore(
Dim cn As New SqlConnection(MyConnectionString
Tr
cn.Open(
Dim cm As New SqlComman
With c
.Connection = c
.CommandType = CommandType.Tex
.CommandText = "RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\a.bak' WITH RECOVERY
.ExecuteNonQuery(
End Wit
MsgBox("Database restored successfully!"
Catch ex As Exceptio
MsgBox(ex.Message
Finall
cn.Close(
End Tr
End Su
I get the "exclusive access could not be obtained.. .. database is in use" error message
I did "Use Master" in query analyser and ran the same restore sql command and it worked fine. I do not know how to use "Use master" here in ado.net. I know it has something to do with sp_Who but not sure how the syntax will fit in.
Note MyConnectionString is something like
"data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ; Password = MyPassword"
Please help. What should I do so that this works in my vb.net app.You can use cn.ChangeDatabase("master") to change database
If you are restoring over an existing database you will also need specify
WITH REPLACE.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:C800D8D0-0D15-4352-8545-9CA47C9B4710@.microsoft.com...
> Hello,
> I am using MSDE with my application and providing our users UI to
backup/restore the database. My app has just 1 database and 1 login mapped
to 1 user (MyAppUser). Backup and restore functionality is using inline sql
commands. Backup works fine with something like this:
> Private Sub Backup()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "BACKUP DATABASE MyDB TO DISK ='D:\Backup\a.bak' WITH INIT"
> .ExecuteNonQuery()
> End With
> MsgBox("Database backed up successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> but when I do restore using something like this:
> Private Sub Restore()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "RESTORE DATABASE MyDB FROM DISK ='D:\Backup\a.bak' WITH RECOVERY"
> .ExecuteNonQuery()
> End With
> MsgBox("Database restored successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> I get the "exclusive access could not be obtained.. .. database is in use"
error message.
> I did "Use Master" in query analyser and ran the same restore sql command
and it worked fine. I do not know how to use "Use master" here in ado.net.
I know it has something to do with sp_Who but not sure how the syntax will
fit in.
> Note MyConnectionString is something like:
> "data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ;
Password = MyPassword"
> Please help. What should I do so that this works in my vb.net app.

"exclusive access could not be obtained.." while restoring

Hello,
I am using MSDE with my application and providing our users UI to backup/res
tore the database. My app has just 1 database and 1 login mapped to 1 user
(MyAppUser). Backup and restore functionality is using inline sql commands.
Backup works fine with so
mething like this:
Private Sub Backup()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "BACKUP DATABASE MyDB TO DISK = 'D:\Backup\a.bak' WITH INIT"
.ExecuteNonQuery()
End With
MsgBox("Database backed up successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
but when I do restore using something like this:
Private Sub Restore()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\a.bak' WITH RE
COVERY"
.ExecuteNonQuery()
End With
MsgBox("Database restored successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
I get the "exclusive access could not be obtained.. .. database is in use" e
rror message.
I did "Use Master" in query analyser and ran the same restore sql command an
d it worked fine. I do not know how to use "Use master" here in ado.net. I
know it has something to do with sp_Who but not sure how the syntax will fi
t in.
Note MyConnectionString is something like:
"data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ; Pa
ssword = MyPassword"
Please help. What should I do so that this works in my vb.net app.You can use cn.ChangeDatabase("master") to change database
If you are restoring over an existing database you will also need specify
WITH REPLACE.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:C800D8D0-0D15-4352-8545-9CA47C9B4710@.microsoft.com...
> Hello,
> I am using MSDE with my application and providing our users UI to
backup/restore the database. My app has just 1 database and 1 login mapped
to 1 user (MyAppUser). Backup and restore functionality is using inline sql
commands. Backup works fine with something like this:
> Private Sub Backup()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "BACKUP DATABASE MyDB TO DISK =
'D:\Backup\a.bak' WITH INIT"
> .ExecuteNonQuery()
> End With
> MsgBox("Database backed up successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> but when I do restore using something like this:
> Private Sub Restore()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "RESTORE DATABASE MyDB FROM DISK =
'D:\Backup\a.bak' WITH RECOVERY"
> .ExecuteNonQuery()
> End With
> MsgBox("Database restored successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> I get the "exclusive access could not be obtained.. .. database is in use"
error message.
> I did "Use Master" in query analyser and ran the same restore sql command
and it worked fine. I do not know how to use "Use master" here in ado.net.
I know it has something to do with sp_Who but not sure how the syntax will
fit in.
> Note MyConnectionString is something like:
> "data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ;
Password = MyPassword"
> Please help. What should I do so that this works in my vb.net app.

"exclusive access could not be obtained.." while restoring

Hello,
I am using MSDE with my application and providing our users UI to backup/restore the database. My app has just 1 database and 1 login mapped to 1 user (MyAppUser). Backup and restore functionality is using inline sql commands. Backup works fine with so
mething like this:
Private Sub Backup()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "BACKUP DATABASE MyDB TO DISK = 'D:\Backup\a.bak' WITH INIT"
.ExecuteNonQuery()
End With
MsgBox("Database backed up successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
but when I do restore using something like this:
Private Sub Restore()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\a.bak' WITH RECOVERY"
.ExecuteNonQuery()
End With
MsgBox("Database restored successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
I get the "exclusive access could not be obtained.. .. database is in use" error message.
I did "Use Master" in query analyser and ran the same restore sql command and it worked fine. I do not know how to use "Use master" here in ado.net. I know it has something to do with sp_Who but not sure how the syntax will fit in.
Note MyConnectionString is something like:
"data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ; Password = MyPassword"
Please help. What should I do so that this works in my vb.net app.
You can use cn.ChangeDatabase("master") to change database
If you are restoring over an existing database you will also need specify
WITH REPLACE.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:C800D8D0-0D15-4352-8545-9CA47C9B4710@.microsoft.com...
> Hello,
> I am using MSDE with my application and providing our users UI to
backup/restore the database. My app has just 1 database and 1 login mapped
to 1 user (MyAppUser). Backup and restore functionality is using inline sql
commands. Backup works fine with something like this:
> Private Sub Backup()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "BACKUP DATABASE MyDB TO DISK =
'D:\Backup\a.bak' WITH INIT"
> .ExecuteNonQuery()
> End With
> MsgBox("Database backed up successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> but when I do restore using something like this:
> Private Sub Restore()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "RESTORE DATABASE MyDB FROM DISK =
'D:\Backup\a.bak' WITH RECOVERY"
> .ExecuteNonQuery()
> End With
> MsgBox("Database restored successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> I get the "exclusive access could not be obtained.. .. database is in use"
error message.
> I did "Use Master" in query analyser and ran the same restore sql command
and it worked fine. I do not know how to use "Use master" here in ado.net.
I know it has something to do with sp_Who but not sure how the syntax will
fit in.
> Note MyConnectionString is something like:
> "data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ;
Password = MyPassword"
> Please help. What should I do so that this works in my vb.net app.

"dbo" user lost after having a database restore

Sometimes (but not always), "dbo" user will be lost after having a database
restore, how can I make the "dbo" user appear again ?Probably because the database was owned by some login which doesn't exist on
the destination SQL Server. Use sp_changedbowner and change the owner to sa
or some other existing login.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c1svec$pq94@.imsp212.netvigator.com...
> Sometimes (but not always), "dbo" user will be lost after having a
database
> restore, how can I make the "dbo" user appear again ?
>

"dbo" user lost after having a database restore

Sometimes (but not always), "dbo" user will be lost after having a database
restore, how can I make the "dbo" user appear again ?Probably because the database was owned by some login which doesn't exist on
the destination SQL Server. Use sp_changedbowner and change the owner to sa
or some other existing login.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c1svec$pq94@.imsp212.netvigator.com...
> Sometimes (but not always), "dbo" user will be lost after having a
database
> restore, how can I make the "dbo" user appear again ?
>

"DB" was not found in TransPublication collection

I had to restore one of the dbs which has replication setup with another SQL,
my problem is that after restoring db I have lot of red "X"s on Publisher
server, and I cannot get ride of those, what am trying to do is to clean this
up and recreat replication but get following errors (one in the subject)
-SQL could not retrieve info about publication
I was even thinking about dropping this db and create again then restore but
get another error msg: cannot delete it's being use for replication.
under replication I see <no items> all errors are in Replication Monitor and
db itself has "hand" indicating it's being use for replication...
need some help?
Thanks
Thanks for responding, but it does not work... I did restore old db and then
was able to clean this up... thanks again
RW
"Paul Ibison" wrote:

> If this database is no longer involved in replication in
> any way, try running sp_removedbreplication.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>