Intermittent Slowdowns Solved by Restart

We are experiencing intermittent slowdowns in SQL Server. Queries that
normally take 2 seconds time out. Restarting the SQL Server process on
the server solves the problem. It will be fine for a few days, maybe a
week, but then the slowdowns will return.
While the slowdown is occurring I have ran trace and profiler and
cannot find any obvious cause. There do not seem to be any other
hungry processes running on the server at this time. The server is
dedictated to SQL.
We run a database maintenance plan nightly to rebuild indexes, backup
the DB, truncate logs, etc.
The server could use some more memory, but it should be sufficient for
our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
use dynamic memory up to a maximum of 1689MB. I know that the server
could benefit from more RAM an additional RAID array to split the
transaction logs and tempdb. However, I believe hardware issues would
lead to overall performance issues and not the intermittent problems I
am seeing.
What are the common causes of sudden, significant performance
degredation on SQL Server that is completely resolved by a restart?
Sounds like you are getting bad query plans. Every time you reindex any
cached plans that access that table are marked for recompile. The next time
you call the query or stored procedure it will be recompiled and get a new
plan. That plan may or may not be different than the last one depending on
the values you pass for the parameters. This is due to the fact the
optimizer uses those values to create the plan based on the estimated number
of rows affected for those values. If you pass in an atypical value (one
that would be best served by a table scan) then all subsequent calls for
that query will use a scan. This link might help to explain:
http://tinyurl.com/l59s
If this happens again run sp_recompile on that sp and see if it helps.
Andrew J. Kelly SQL MVP
"DaveR" <NOSPAM_drubin@.NOSPAM_i-2000.com> wrote in message
news:E8omROaejtWXl2wvEqD0hlR0=fxm@.4ax.com...
> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?
|||You may want to check the contents of tempdb. Locked resources that are not
released properly can accumulate, increasing contention and slowing response
times. Whilst this would not identify the root cause of the problems you're
seeing, it would explain why a restart would improve performance - tempdb is
cleared down whenever the server is restarted.
"DaveR" wrote:

> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?
>

Intermittent Slowdowns Solved by Restart

We are experiencing intermittent slowdowns in SQL Server. Queries that
normally take 2 seconds time out. Restarting the SQL Server process on
the server solves the problem. It will be fine for a few days, maybe a
week, but then the slowdowns will return.
While the slowdown is occurring I have ran trace and profiler and
cannot find any obvious cause. There do not seem to be any other
hungry processes running on the server at this time. The server is
dedictated to SQL.
We run a database maintenance plan nightly to rebuild indexes, backup
the DB, truncate logs, etc.
The server could use some more memory, but it should be sufficient for
our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
use dynamic memory up to a maximum of 1689MB. I know that the server
could benefit from more RAM an additional RAID array to split the
transaction logs and tempdb. However, I believe hardware issues would
lead to overall performance issues and not the intermittent problems I
am seeing.
What are the common causes of sudden, significant performance
degredation on SQL Server that is completely resolved by a restart?Sounds like you are getting bad query plans. Every time you reindex any
cached plans that access that table are marked for recompile. The next time
you call the query or stored procedure it will be recompiled and get a new
plan. That plan may or may not be different than the last one depending on
the values you pass for the parameters. This is due to the fact the
optimizer uses those values to create the plan based on the estimated number
of rows affected for those values. If you pass in an atypical value (one
that would be best served by a table scan) then all subsequent calls for
that query will use a scan. This link might help to explain:
http://tinyurl.com/l59s
If this happens again run sp_recompile on that sp and see if it helps.
Andrew J. Kelly SQL MVP
"DaveR" <NOSPAM_drubin@.NOSPAM_i-2000.com> wrote in message
news:E8omROaejtWXl2wvEqD0hlR0=fxm@.4ax.com...
> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?|||You may want to check the contents of tempdb. Locked resources that are not
released properly can accumulate, increasing contention and slowing response
times. Whilst this would not identify the root cause of the problems you're
seeing, it would explain why a restart would improve performance - tempdb is
cleared down whenever the server is restarted.
"DaveR" wrote:

> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?
>

Intermittent Slowdowns Solved by Restart

We are experiencing intermittent slowdowns in SQL Server. Queries that
normally take 2 seconds time out. Restarting the SQL Server process on
the server solves the problem. It will be fine for a few days, maybe a
week, but then the slowdowns will return.
While the slowdown is occurring I have ran trace and profiler and
cannot find any obvious cause. There do not seem to be any other
hungry processes running on the server at this time. The server is
dedictated to SQL.
We run a database maintenance plan nightly to rebuild indexes, backup
the DB, truncate logs, etc.
The server could use some more memory, but it should be sufficient for
our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
use dynamic memory up to a maximum of 1689MB. I know that the server
could benefit from more RAM an additional RAID array to split the
transaction logs and tempdb. However, I believe hardware issues would
lead to overall performance issues and not the intermittent problems I
am seeing.
What are the common causes of sudden, significant performance
degredation on SQL Server that is completely resolved by a restart?Sounds like you are getting bad query plans. Every time you reindex any
cached plans that access that table are marked for recompile. The next time
you call the query or stored procedure it will be recompiled and get a new
plan. That plan may or may not be different than the last one depending on
the values you pass for the parameters. This is due to the fact the
optimizer uses those values to create the plan based on the estimated number
of rows affected for those values. If you pass in an atypical value (one
that would be best served by a table scan) then all subsequent calls for
that query will use a scan. This link might help to explain:
http://tinyurl.com/l59s
If this happens again run sp_recompile on that sp and see if it helps.
--
Andrew J. Kelly SQL MVP
"DaveR" <NOSPAM_drubin@.NOSPAM_i-2000.com> wrote in message
news:E8omROaejtWXl2wvEqD0hlR0=fxm@.4ax.com...
> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?|||You may want to check the contents of tempdb. Locked resources that are not
released properly can accumulate, increasing contention and slowing response
times. Whilst this would not identify the root cause of the problems you're
seeing, it would explain why a restart would improve performance - tempdb is
cleared down whenever the server is restarted.
"DaveR" wrote:
> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?
>

Intermittent query slowdowns and corresponding high CPU utilization

Hi. We are experiencing an issue on SQL Server that sounds very simlar to
the hotifx detailed here: http://support.microsoft.com/kb/835864
Only trouble is we are running on a single CPU server when the article
suggests it is only applicable to multple CPU servers. Has anyone had
experience with this issue and resolving it?
--
McGeeky
http://mcgeeky.blogspot.comOn Fri, 28 Apr 2006 14:55:11 +0100, "McGeeky" <anon@.anon.com> wrote:
>Hi. We are experiencing an issue on SQL Server that sounds very simlar to
>the hotifx detailed here: http://support.microsoft.com/kb/835864
>Only trouble is we are running on a single CPU server when the article
>suggests it is only applicable to multple CPU servers. Has anyone had
>experience with this issue and resolving it?
I may be seeing it myself on a small, old two-processor box running on
only 512mb. Thanks for pointing out the article.
Microsoft is infamous for admitting to one special-case of bugs that
turn up in about a million other environments.
Doesn't guarantee that what you or I see is the same bug, since even
Microsoft may not have verified all the situations.
Josh|||Do you see it happen to any query? We are experiencing it only on one
query - it generally takes only 5-10 seconds but occassionally SQL Server
goes nuts and it takes 20 minutes with CPU on 100%.
--
McGeeky
http://mcgeeky.blogspot.com
"jxstern" <jxstern@.wherever.com> wrote in message
news:t5n452t4bn1h2r4f7iv4cpaqbutsch2skv@.4ax.com...
> On Fri, 28 Apr 2006 14:55:11 +0100, "McGeeky" <anon@.anon.com> wrote:
>>Hi. We are experiencing an issue on SQL Server that sounds very simlar to
>>the hotifx detailed here: http://support.microsoft.com/kb/835864
>>Only trouble is we are running on a single CPU server when the article
>>suggests it is only applicable to multple CPU servers. Has anyone had
>>experience with this issue and resolving it?
>
> I may be seeing it myself on a small, old two-processor box running on
> only 512mb. Thanks for pointing out the article.
> Microsoft is infamous for admitting to one special-case of bugs that
> turn up in about a million other environments.
> Doesn't guarantee that what you or I see is the same bug, since even
> Microsoft may not have verified all the situations.
> Josh|||On Tue, 2 May 2006 12:25:06 +0100, "McGeeky" <anon@.anon.com> wrote:
>Do you see it happen to any query? We are experiencing it only on one
>query - it generally takes only 5-10 seconds but occassionally SQL Server
>goes nuts and it takes 20 minutes with CPU on 100%.
Not at my current location.
Saw something that might have been this at a previous shop, where an
SP in a particular test (load) script goes nuts, but attempts to rerun
with even the same data via query analyzer see no problem.
There were a lot of executions of prepared plans involved, we might
have mismapped the numbers to the source, should have run a test with
SP statement start and plan capture set in profiler, so I can't be
ENTIRELY certain. And even if it was what it seemed, the cause might
not have been the memory allocation issue mentioned in the MS
document, since this wasn't really a high-transaction system.
Josh|||Your scenario sounds exactly like mine: its one query in particular but
attempts to run it in query analyzer with the exact same parameters fail to
reproduce the problem.
I like your idea of setting up a trace on the stored procedure plan. A
possibility is that SQL Server gets mixed up occasionally and creates a
really bad access plan.
--
McGeeky
http://mcgeeky.blogspot.com
"jxstern" <jxstern@.wherever.com> wrote in message
news:chsf521jm3l6e86tamq0c6t0covng5eg4m@.4ax.com...
> On Tue, 2 May 2006 12:25:06 +0100, "McGeeky" <anon@.anon.com> wrote:
>>Do you see it happen to any query? We are experiencing it only on one
>>query - it generally takes only 5-10 seconds but occassionally SQL Server
>>goes nuts and it takes 20 minutes with CPU on 100%.
> Not at my current location.
> Saw something that might have been this at a previous shop, where an
> SP in a particular test (load) script goes nuts, but attempts to rerun
> with even the same data via query analyzer see no problem.
> There were a lot of executions of prepared plans involved, we might
> have mismapped the numbers to the source, should have run a test with
> SP statement start and plan capture set in profiler, so I can't be
> ENTIRELY certain. And even if it was what it seemed, the cause might
> not have been the memory allocation issue mentioned in the MS
> document, since this wasn't really a high-transaction system.
> Josh|||Can you capture plans in SQL Server 2000?
--
McGeeky
http://mcgeeky.blogspot.com
"jxstern" <jxstern@.wherever.com> wrote in message
news:chsf521jm3l6e86tamq0c6t0covng5eg4m@.4ax.com...
> On Tue, 2 May 2006 12:25:06 +0100, "McGeeky" <anon@.anon.com> wrote:
>>Do you see it happen to any query? We are experiencing it only on one
>>query - it generally takes only 5-10 seconds but occassionally SQL Server
>>goes nuts and it takes 20 minutes with CPU on 100%.
> Not at my current location.
> Saw something that might have been this at a previous shop, where an
> SP in a particular test (load) script goes nuts, but attempts to rerun
> with even the same data via query analyzer see no problem.
> There were a lot of executions of prepared plans involved, we might
> have mismapped the numbers to the source, should have run a test with
> SP statement start and plan capture set in profiler, so I can't be
> ENTIRELY certain. And even if it was what it seemed, the cause might
> not have been the memory allocation issue mentioned in the MS
> document, since this wasn't really a high-transaction system.
> Josh

intermittent locks

We are experiencing problems intermittently.

Users report problems of various types including timeout messages. We investigate and find a user has acquired a lock which is blocking other users.

We contact the user and they have usually completed their activity and are not always aware of any problem despite them owning a lock.

When the user logs out of the application the lock clears and the system returns to normal.

Indexes have been rebuilt, auto update statistics is on.

Does anyone have any suggestions? :cool:The first thing I'd do is start watching for locks to determine how often they occur, and ask the users if they know of any activity that causes the problems associated with locking/blocking (that may give you clues about what you need to watch).

Once you understand what you are looking for, run a trace using SQL Profiler at the same time as a Performance Monitor trace watching for locking/blocking. The PerfMon trace will show you when the problem occurs, the Profiler trace will show you what caused the problem.

When you understand the cause of the problem, you can then look at changing the application to avoid the problem.

-PatP|||We are trying to gather more information from the users to track this down.

Anecdotally users believe that they have finished their activity and are simply still logged on or are running searches.

We haven't needed to kill a session, the user simply logs off.

It's almost as though the lock has been taken but not released when the activity has finished.

Does this sound likely/possible? If so any ideas what could be causing it?|||Does this sound likely/possible? If so any ideas what could be causing it?Yes, it sounds rather likely.

I'd suspect that the problem is something that the code is doing "behind the curtains" that the user is completely unaware of, but is still causing havok. Until you can compare the two traces (or provide LOTS of additional insight into your application and server configuration), we can only guess.

-PatP

intermittent connectivity issues in virtual server images

Hello,
I have a few virtual server images running for development purposes and
have been experiencing intermittent connectivity issues for quite a
while. First of all my configuration...
VPC image 1:
Windows Server 2003 Enterprise Edition SP1
Being used as domain controller and DNS server for all other images
VPC image 2:
Windows Server 2003 Enterprise Edition SP1
SQL Server Enterprise Edition 2000 SP3 (8.00.760)
VPC image 3:
Windows Server 2003 Enterprise Edition SP1
IIS 6.0
.NET Framework 1.0 (for ASP.NET stuff)
All these images are running on a Pentium M 1.75ghz with 2GB of RAM
(which seems to be enough horsepower to handle everything) and using
Microsoft Virtual PC 5.3.582.27 under Windows XP Pro SP2.
On to my problem...
Intermittently, an ASP.NET application I wrote would error out upon
trying to establish a connection to the SQL Server (using
System.Data.SQLClient). I return the error...
"System.Data.SqlClient.SqlException: SQL Server does not exist or
access denied"
...the stack trace doesn't provide much insight as to why. This error
will generally occur multiple times until an attempt succeeds and then
it appears to keep working as long as I don't leave the system idle too
long (i.e. twenty minutes or so).
This could of course be caused by numorous virtual networking problems
but we can eliminate all of that (I think) by this second issue.
Today I attempted to import some data via the DTS Import/Export Wizard
in Enterprise Manager, as soon as I attempt to click Next on the
"Choose a Data Source" dialog I receive...
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server
does not exist or access denied.
Context: Error during initialization of the provider.
This is occuring while running EM on the Virtual Image containing SQL
Server. Someone with more knowledge of how SQL Server establishes
named pipes connections may be able to determine if this eliminates the
virtual network as a culpret or not (I'm not positive it does).
Anyway, ideas?
Thanks,
Ben.
PS I should add, while the errors are being thrown I am still able to
ping the sql virtual image from my iis virtual image. I also have
another ASP.NET app (MS CRM) that is able to communicate with the
server the whole time and I am able to establish a Query Analyzer
connection without fail (using both integrated and sql server
authentication). I'm about stumped.<ben.usenet.alias@.gmail.com> wrote in message
news:1146078164.773174.237320@.j33g2000cwa.googlegroups.com...
> Hello,
> I have a few virtual server images running for development purposes and
> have been experiencing intermittent connectivity issues for quite a
> while. First of all my configuration...
> VPC image 1:
> Windows Server 2003 Enterprise Edition SP1
> Being used as domain controller and DNS server for all other images
> VPC image 2:
> Windows Server 2003 Enterprise Edition SP1
> SQL Server Enterprise Edition 2000 SP3 (8.00.760)
> VPC image 3:
> Windows Server 2003 Enterprise Edition SP1
> IIS 6.0
> .NET Framework 1.0 (for ASP.NET stuff)
> All these images are running on a Pentium M 1.75ghz with 2GB of RAM
> (which seems to be enough horsepower to handle everything) and using
> Microsoft Virtual PC 5.3.582.27 under Windows XP Pro SP2.
> On to my problem...
> Intermittently, an ASP.NET application I wrote would error out upon
> trying to establish a connection to the SQL Server (using
> System.Data.SQLClient). I return the error...
> "System.Data.SqlClient.SqlException: SQL Server does not exist or
> access denied"
> ...the stack trace doesn't provide much insight as to why. This error
> will generally occur multiple times until an attempt succeeds and then
> it appears to keep working as long as I don't leave the system idle too
> long (i.e. twenty minutes or so).
> This could of course be caused by numorous virtual networking problems
> but we can eliminate all of that (I think) by this second issue.
> Today I attempted to import some data via the DTS Import/Export Wizard
> in Enterprise Manager, as soon as I attempt to click Next on the
> "Choose a Data Source" dialog I receive...
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Serv
er
> does not exist or access denied.
> Context: Error during initialization of the provider.
> This is occuring while running EM on the Virtual Image containing SQL
> Server. Someone with more knowledge of how SQL Server establishes
> named pipes connections may be able to determine if this eliminates the
> virtual network as a culpret or not (I'm not positive it does).
>
What network are the VM's bound to? Your host OS's wireless network?
Here's what I do
1) Install the Loopback Adapter in the Host OS|||<ben.usenet.alias@.gmail.com> wrote in message
news:1146078164.773174.237320@.j33g2000cwa.googlegroups.com...
> Hello,
> I have a few virtual server images running for development purposes and
> have been experiencing intermittent connectivity issues for quite a
> while. First of all my configuration...
> VPC image 1:
> Windows Server 2003 Enterprise Edition SP1
> Being used as domain controller and DNS server for all other images
> VPC image 2:
> Windows Server 2003 Enterprise Edition SP1
> SQL Server Enterprise Edition 2000 SP3 (8.00.760)
> VPC image 3:
> Windows Server 2003 Enterprise Edition SP1
> IIS 6.0
> .NET Framework 1.0 (for ASP.NET stuff)
> All these images are running on a Pentium M 1.75ghz with 2GB of RAM
> (which seems to be enough horsepower to handle everything) and using
> Microsoft Virtual PC 5.3.582.27 under Windows XP Pro SP2.
> On to my problem...
> Intermittently, an ASP.NET application I wrote would error out upon
> trying to establish a connection to the SQL Server (using
> System.Data.SQLClient). I return the error...
> "System.Data.SqlClient.SqlException: SQL Server does not exist or
> access denied"
> ...the stack trace doesn't provide much insight as to why. This error
> will generally occur multiple times until an attempt succeeds and then
> it appears to keep working as long as I don't leave the system idle too
> long (i.e. twenty minutes or so).
> This could of course be caused by numorous virtual networking problems
> but we can eliminate all of that (I think) by this second issue.
> Today I attempted to import some data via the DTS Import/Export Wizard
> in Enterprise Manager, as soon as I attempt to click Next on the
> "Choose a Data Source" dialog I receive...
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Serv
er
> does not exist or access denied.
> Context: Error during initialization of the provider.
> This is occuring while running EM on the Virtual Image containing SQL
> Server. Someone with more knowledge of how SQL Server establishes
> named pipes connections may be able to determine if this eliminates the
> virtual network as a culpret or not (I'm not positive it does).
> Anyway, ideas?
Oops.
Here's what I do to connect my VM's to each other and my host OS.
-Install the Looblack Adapter in the host OS
How to install the Microsoft Loopback adapter in Windows XP
http://support.microsoft.com/defaul...kb;en-us;839013
Assign a private IP address to the Loopback adapter. I use 10.240.0.1 mask
255.255.255.0
Then in Virtual Server add a Virtual Network for that adapter and enable the
virtual server DHCP server and let DHCP assign a range of the 10.240.0.X
network, reserving some address for VM's with fixed IP's.
David|||I like that idea. I've been toying around with the idea of isolating
my test environment further (putting it on it's own subnet) but I still
do need to connect my virtual servers to the internet for some items.
I'm considering adding a second virtual NIC on my virtual domain
controller. I'd run the internal against the loopback adapter (as you
described) and the second virtual NIC against my laptop's wired NIC.
Then I'd throw ISA on the virtual domain controller to route outside
traffic. Everything on the inside would exist on 10.x.x.x addys with a
255.255.255.0 netmask. The DNS on my virtual domain controller already
forwards unhandled requests to the corporate DNS here and that would
continue as is. The only downside I see to this is that I wouldn't be
able to hit my virtual server's web service from my laptop (I like to
point my laptop's browser at my virtual server running IIS to test
client functionality).
As to my original problem, I think I'm close to a solution (unrelated
to this conversation). I'll post more when I have something concrete
so that other's can benefit from it.
Ben.

intermittent connectivity issues in virtual server images

Hello,
I have a few virtual server images running for development purposes and
have been experiencing intermittent connectivity issues for quite a
while. First of all my configuration...
VPC image 1:
Windows Server 2003 Enterprise Edition SP1
Being used as domain controller and DNS server for all other images
VPC image 2:
Windows Server 2003 Enterprise Edition SP1
SQL Server Enterprise Edition 2000 SP3 (8.00.760)
VPC image 3:
Windows Server 2003 Enterprise Edition SP1
IIS 6.0
.NET Framework 1.0 (for ASP.NET stuff)
All these images are running on a Pentium M 1.75ghz with 2GB of RAM
(which seems to be enough horsepower to handle everything) and using
Microsoft Virtual PC 5.3.582.27 under Windows XP Pro SP2.
On to my problem...
Intermittently, an ASP.NET application I wrote would error out upon
trying to establish a connection to the SQL Server (using
System.Data.SQLClient). I return the error...
"System.Data.SqlClient.SqlException: SQL Server does not exist or
access denied"
...the stack trace doesn't provide much insight as to why. This error
will generally occur multiple times until an attempt succeeds and then
it appears to keep working as long as I don't leave the system idle too
long (i.e. twenty minutes or so).
This could of course be caused by numorous virtual networking problems
but we can eliminate all of that (I think) by this second issue.
Today I attempted to import some data via the DTS Import/Export Wizard
in Enterprise Manager, as soon as I attempt to click Next on the
"Choose a Data Source" dialog I receive...
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server
does not exist or access denied.
Context: Error during initialization of the provider.
This is occuring while running EM on the Virtual Image containing SQL
Server. Someone with more knowledge of how SQL Server establishes
named pipes connections may be able to determine if this eliminates the
virtual network as a culpret or not (I'm not positive it does).
Anyway, ideas?
Thanks,
Ben.
PS I should add, while the errors are being thrown I am still able to
ping the sql virtual image from my iis virtual image. I also have
another ASP.NET app (MS CRM) that is able to communicate with the
server the whole time and I am able to establish a Query Analyzer
connection without fail (using both integrated and sql server
authentication). I'm about stumped.<ben.usenet.alias@.gmail.com> wrote in message
news:1146078164.773174.237320@.j33g2000cwa.googlegroups.com...
> Hello,
> I have a few virtual server images running for development purposes and
> have been experiencing intermittent connectivity issues for quite a
> while. First of all my configuration...
> VPC image 1:
> Windows Server 2003 Enterprise Edition SP1
> Being used as domain controller and DNS server for all other images
> VPC image 2:
> Windows Server 2003 Enterprise Edition SP1
> SQL Server Enterprise Edition 2000 SP3 (8.00.760)
> VPC image 3:
> Windows Server 2003 Enterprise Edition SP1
> IIS 6.0
> .NET Framework 1.0 (for ASP.NET stuff)
> All these images are running on a Pentium M 1.75ghz with 2GB of RAM
> (which seems to be enough horsepower to handle everything) and using
> Microsoft Virtual PC 5.3.582.27 under Windows XP Pro SP2.
> On to my problem...
> Intermittently, an ASP.NET application I wrote would error out upon
> trying to establish a connection to the SQL Server (using
> System.Data.SQLClient). I return the error...
> "System.Data.SqlClient.SqlException: SQL Server does not exist or
> access denied"
> ...the stack trace doesn't provide much insight as to why. This error
> will generally occur multiple times until an attempt succeeds and then
> it appears to keep working as long as I don't leave the system idle too
> long (i.e. twenty minutes or so).
> This could of course be caused by numorous virtual networking problems
> but we can eliminate all of that (I think) by this second issue.
> Today I attempted to import some data via the DTS Import/Export Wizard
> in Enterprise Manager, as soon as I attempt to click Next on the
> "Choose a Data Source" dialog I receive...
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server
> does not exist or access denied.
> Context: Error during initialization of the provider.
> This is occuring while running EM on the Virtual Image containing SQL
> Server. Someone with more knowledge of how SQL Server establishes
> named pipes connections may be able to determine if this eliminates the
> virtual network as a culpret or not (I'm not positive it does).
>
What network are the VM's bound to? Your host OS's wireless network?
Here's what I do
1) Install the Loopback Adapter in the Host OS|||<ben.usenet.alias@.gmail.com> wrote in message
news:1146078164.773174.237320@.j33g2000cwa.googlegroups.com...
> Hello,
> I have a few virtual server images running for development purposes and
> have been experiencing intermittent connectivity issues for quite a
> while. First of all my configuration...
> VPC image 1:
> Windows Server 2003 Enterprise Edition SP1
> Being used as domain controller and DNS server for all other images
> VPC image 2:
> Windows Server 2003 Enterprise Edition SP1
> SQL Server Enterprise Edition 2000 SP3 (8.00.760)
> VPC image 3:
> Windows Server 2003 Enterprise Edition SP1
> IIS 6.0
> .NET Framework 1.0 (for ASP.NET stuff)
> All these images are running on a Pentium M 1.75ghz with 2GB of RAM
> (which seems to be enough horsepower to handle everything) and using
> Microsoft Virtual PC 5.3.582.27 under Windows XP Pro SP2.
> On to my problem...
> Intermittently, an ASP.NET application I wrote would error out upon
> trying to establish a connection to the SQL Server (using
> System.Data.SQLClient). I return the error...
> "System.Data.SqlClient.SqlException: SQL Server does not exist or
> access denied"
> ...the stack trace doesn't provide much insight as to why. This error
> will generally occur multiple times until an attempt succeeds and then
> it appears to keep working as long as I don't leave the system idle too
> long (i.e. twenty minutes or so).
> This could of course be caused by numorous virtual networking problems
> but we can eliminate all of that (I think) by this second issue.
> Today I attempted to import some data via the DTS Import/Export Wizard
> in Enterprise Manager, as soon as I attempt to click Next on the
> "Choose a Data Source" dialog I receive...
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server
> does not exist or access denied.
> Context: Error during initialization of the provider.
> This is occuring while running EM on the Virtual Image containing SQL
> Server. Someone with more knowledge of how SQL Server establishes
> named pipes connections may be able to determine if this eliminates the
> virtual network as a culpret or not (I'm not positive it does).
> Anyway, ideas?
Oops.
Here's what I do to connect my VM's to each other and my host OS.
-Install the Looblack Adapter in the host OS
How to install the Microsoft Loopback adapter in Windows XP
http://support.microsoft.com/default.aspx?scid=kb;en-us;839013
Assign a private IP address to the Loopback adapter. I use 10.240.0.1 mask
255.255.255.0
Then in Virtual Server add a Virtual Network for that adapter and enable the
virtual server DHCP server and let DHCP assign a range of the 10.240.0.X
network, reserving some address for VM's with fixed IP's.
David|||I like that idea. I've been toying around with the idea of isolating
my test environment further (putting it on it's own subnet) but I still
do need to connect my virtual servers to the internet for some items.
I'm considering adding a second virtual NIC on my virtual domain
controller. I'd run the internal against the loopback adapter (as you
described) and the second virtual NIC against my laptop's wired NIC.
Then I'd throw ISA on the virtual domain controller to route outside
traffic. Everything on the inside would exist on 10.x.x.x addys with a
255.255.255.0 netmask. The DNS on my virtual domain controller already
forwards unhandled requests to the corporate DNS here and that would
continue as is. The only downside I see to this is that I wouldn't be
able to hit my virtual server's web service from my laptop (I like to
point my laptop's browser at my virtual server running IIS to test
client functionality).
As to my original problem, I think I'm close to a solution (unrelated
to this conversation). I'll post more when I have something concrete
so that other's can benefit from it.
Ben.

Intermittent Connection Problems with Sql Server

I am experiencing intermittent connection problems with the Northwind database and sql server express. The most frequent error message indicates that I am already logged in and will not allow another user.

The odd thing is that even when I am locked out I can access a Northwind database from another connection on Server Explorer.

I ran code to demonstrate simple binding for two text boxes, no problem. I added a project to show complex binding using the datagrid view tool, but could not get past selecting the database.

I tried the same simple binding project on another computer running a beta version of VS 2005 Pro and could not connect to Northwind on that computer also. (Two different Northwind databases, different computers)

A couple days ago I restored the database in Solutions Manager and that helped for a while, but I should not have to resort to that, I know.

How do I remove the user lock? I tried Solutions Manager Express with the first computer but could not access the database in the management module.

The exact text of the error would help. It sounds like you might be trying to access the data through both a User Instance and through the parent instance. This doesn't work since only one copy of SQL can access a database at a time.

Mike

|||

Mike-

I learned that whenever I had a problem with connecting to a database, it was because I was using the dataset from the prior project in the solution, in a new project.

Thanks for replying.

Ruel

P.S.

I don't know if I should start a new thread, but how do I pull data from an Excel spreadsheet into a sql server database table?

|||

Hi,

i would suggest to refer

http://blogs.msdn.com/euanga/archive/2006/07/20/672272.aspx

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=293466&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1088614&SiteID=1

HTH

Hemantgiri S. Goswami

|||

Thanks Hemantgiri -

The links were helpful for understanding the process of pulling data files into sql server. However, I am still running into a roadblock. For instance, the following:

===================================================================

bulk insert

AAS Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads'

===================================================================

Where:

AAS = Database Name

Accounts = Table Name

Gives the following error message:

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near 'Accounts'.

|||

Hi,

run it like,

bulk insert

AAS..Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads'

or

bulk insert

AAS.ObjectOwner.Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads'

Hemantgiri S. Goswami

|||

Tried the following as you suggested

bulk insert AAS..Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads\2a20070112.xls'

With the resulting error message:

Msg 4863, Level 16, State 1, Line 1

Bulk load data conversion error (truncation) for row 1, column 1 (AccountNo).

Column 1 of table Accounts is nchar(15) - I raised it from 10 to 15 to see if that would take care of the truncation issue.

The format for column 1 of the xls file is general.

I have had a similar experience with an xml file.

I also tried the bcp utility to format a file for input but I get a message telling me that either the server does not exist or I do not have access.

Thanks for your assistance

Ruel

|||

Could not get to the server with the bcp utility so I tried bulk insert. I found that I had to use "firstrow=2" to get past error messages.

bulk insert

AAS..Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads\2a20070112.xls'

with

(

firstrow=2

)

However, the above returns the following message in SSMSE:

(0 row(s) affected)

|||

Hi,

Could ensure the length of the all field!!!!

Hemantgiri S. Goswami

Intermittent connection dramas: "Cannot open user default database..."

Hi,
I've been experiencing some very strange, intermittent failures connecting
to an MS SQL database... basically at seemingly random times the server
decides that it can't open the requested database (reporting error 4062:
"Cannot open user default database. Using master database instead"). I've
so far been unable to track down any useful background to this error
message; has anyone on this group come across similar problems? Any clues
as to what might be triggering this sort of fault? More specific details
follow:
* In the majority of cases, connection is established correctly (albeit
sometimes a little slowly).
* Connections are being made to the local machine, using straightforward,
static code. The problem isn't something trivial like a mis-typed
database name, incorrect password etc. Re-running the SAME CODE will
eventually succeed.
* The server isn't particularly heavily loaded, and I can perceive no
pattern to the fault (it seems no more prevalent when the
server's busy, nor after any particular events).
* Nothing relevant is documented in the Event Viewer, nor the server logs.
Am I looking in the right place?
* We're running SQL Server 2005 Express Edition, latest service pack...
are there any connection rate limitations on this software? All
documentation I've seen indicates that there are not, but this behaviour
almost suggests that there may be...
* Connecting (from local machine) using PHP mssql module (latest stable
version).
* Although possibly symptomatic of a DoS, there's no other evidence to
suggest that this is the case (TCP to the service is firewalled).
Would be grateful for any advice on this issue.
Cheers,
Alex.
Alex Craven wrote:
> Hi,
> I've been experiencing some very strange, intermittent failures connecting
> to an MS SQL database... basically at seemingly random times the server
> decides that it can't open the requested database (reporting error 4062:
> "Cannot open user default database. Using master database instead"). I've
> so far been unable to track down any useful background to this error
> message; has anyone on this group come across similar problems? Any clues
> as to what might be triggering this sort of fault? More specific details
> follow:
> * In the majority of cases, connection is established correctly (albeit
> sometimes a little slowly).
> * Connections are being made to the local machine, using straightforward,
> static code. The problem isn't something trivial like a mis-typed
> database name, incorrect password etc. Re-running the SAME CODE will
> eventually succeed.
> * The server isn't particularly heavily loaded, and I can perceive no
> pattern to the fault (it seems no more prevalent when the
> server's busy, nor after any particular events).
> * Nothing relevant is documented in the Event Viewer, nor the server logs.
> Am I looking in the right place?
> * We're running SQL Server 2005 Express Edition, latest service pack...
> are there any connection rate limitations on this software? All
> documentation I've seen indicates that there are not, but this behaviour
> almost suggests that there may be...
> * Connecting (from local machine) using PHP mssql module (latest stable
> version).
> * Although possibly symptomatic of a DoS, there's no other evidence to
> suggest that this is the case (TCP to the service is firewalled).
>
> Would be grateful for any advice on this issue.
> Cheers,
> Alex.
>
>
Make sure the "problem" database isn't configured for auto-close, also
make sure it's not the target for log-shipping, or some other process
that might take it offline to do a restore, etc.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Do you periodically rename, remove, or detach the database?
Do you have any jobs that put the database into single-user mode or
otherwise alter the availability of the database?
|||On Fri, 29 Sep 2006 08:14:30 -0500, Tracy McKibben uttered:
[...]
> Make sure the "problem" database isn't configured for auto-close, also
> make sure it's not the target for log-shipping, or some other process
> that might take it offline to do a restore, etc.
Hi Tracy et al,
Thanks for all your advice; so far it looks like the auto-close 'feature'
was most likely the root of my problems (or at least a major factor...);
the system seems to be performing rather more reliably with that disabled.
Cheers,
Alex.
|||Alex Craven wrote:
> On Fri, 29 Sep 2006 08:14:30 -0500, Tracy McKibben uttered:
> [...]
>
> Hi Tracy et al,
> Thanks for all your advice; so far it looks like the auto-close 'feature'
> was most likely the root of my problems (or at least a major factor...);
> the system seems to be performing rather more reliably with that disabled.
> Cheers,
> Alex.
>
That auto-close option should NEVER be enabled for a production
database. Another one to watch out for is auto-shrink.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
sql

Intermittent connection dramas: "Cannot open user default database..."

Hi,
I've been experiencing some very strange, intermittent failures connecting
to an MS SQL database... basically at seemingly random times the server
decides that it can't open the requested database (reporting error 4062:
"Cannot open user default database. Using master database instead"). I've
so far been unable to track down any useful background to this error
message; has anyone on this group come across similar problems? Any clues
as to what might be triggering this sort of fault? More specific details
follow:
* In the majority of cases, connection is established correctly (albeit
sometimes a little slowly).
* Connections are being made to the local machine, using straightforward,
static code. The problem isn't something trivial like a mis-typed
database name, incorrect password etc. Re-running the SAME CODE will
eventually succeed.
* The server isn't particularly heavily loaded, and I can perceive no
pattern to the fault (it seems no more prevalent when the
server's busy, nor after any particular events).
* Nothing relevant is documented in the Event Viewer, nor the server logs.
Am I looking in the right place'
* We're running SQL Server 2005 Express Edition, latest service pack...
are there any connection rate limitations on this software? All
documentation I've seen indicates that there are not, but this behaviour
almost suggests that there may be...
* Connecting (from local machine) using PHP mssql module (latest stable
version).
* Although possibly symptomatic of a DoS, there's no other evidence to
suggest that this is the case (TCP to the service is firewalled).
Would be grateful for any advice on this issue.
Cheers,
Alex.HI Alex
Have you checked for Anti-virus or intrusion detection software in the
server, this can cause connections to fail. If you have the autoclose option
set for the database or attaching the database when you connect then if the
AV software is scanning the files you will not be able to do this.
You may also want to eliminate any particular machine or see if this occurs
on the server, to rule out general networking issues. Check firewalls are
correctly configured and if you are using group policies that they are not
interfering.
You may also want to check if you are service packed to a recent version.
John
"Alex Craven" wrote:
> Hi,
> I've been experiencing some very strange, intermittent failures connecting
> to an MS SQL database... basically at seemingly random times the server
> decides that it can't open the requested database (reporting error 4062:
> "Cannot open user default database. Using master database instead"). I've
> so far been unable to track down any useful background to this error
> message; has anyone on this group come across similar problems? Any clues
> as to what might be triggering this sort of fault? More specific details
> follow:
> * In the majority of cases, connection is established correctly (albeit
> sometimes a little slowly).
> * Connections are being made to the local machine, using straightforward,
> static code. The problem isn't something trivial like a mis-typed
> database name, incorrect password etc. Re-running the SAME CODE will
> eventually succeed.
> * The server isn't particularly heavily loaded, and I can perceive no
> pattern to the fault (it seems no more prevalent when the
> server's busy, nor after any particular events).
> * Nothing relevant is documented in the Event Viewer, nor the server logs.
> Am I looking in the right place'
> * We're running SQL Server 2005 Express Edition, latest service pack...
> are there any connection rate limitations on this software? All
> documentation I've seen indicates that there are not, but this behaviour
> almost suggests that there may be...
> * Connecting (from local machine) using PHP mssql module (latest stable
> version).
> * Although possibly symptomatic of a DoS, there's no other evidence to
> suggest that this is the case (TCP to the service is firewalled).
>
> Would be grateful for any advice on this issue.
> Cheers,
> Alex.
>
>|||Alex Craven wrote:
> Hi,
> I've been experiencing some very strange, intermittent failures connecting
> to an MS SQL database... basically at seemingly random times the server
> decides that it can't open the requested database (reporting error 4062:
> "Cannot open user default database. Using master database instead"). I've
> so far been unable to track down any useful background to this error
> message; has anyone on this group come across similar problems? Any clues
> as to what might be triggering this sort of fault? More specific details
> follow:
> * In the majority of cases, connection is established correctly (albeit
> sometimes a little slowly).
> * Connections are being made to the local machine, using straightforward,
> static code. The problem isn't something trivial like a mis-typed
> database name, incorrect password etc. Re-running the SAME CODE will
> eventually succeed.
> * The server isn't particularly heavily loaded, and I can perceive no
> pattern to the fault (it seems no more prevalent when the
> server's busy, nor after any particular events).
> * Nothing relevant is documented in the Event Viewer, nor the server logs.
> Am I looking in the right place'
> * We're running SQL Server 2005 Express Edition, latest service pack...
> are there any connection rate limitations on this software? All
> documentation I've seen indicates that there are not, but this behaviour
> almost suggests that there may be...
> * Connecting (from local machine) using PHP mssql module (latest stable
> version).
> * Although possibly symptomatic of a DoS, there's no other evidence to
> suggest that this is the case (TCP to the service is firewalled).
>
> Would be grateful for any advice on this issue.
> Cheers,
> Alex.
>
>
Make sure the "problem" database isn't configured for auto-close, also
make sure it's not the target for log-shipping, or some other process
that might take it offline to do a restore, etc.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Do you periodically rename, remove, or detach the database?
Do you have any jobs that put the database into single-user mode or
otherwise alter the availability of the database?|||On Fri, 29 Sep 2006 08:14:30 -0500, Tracy McKibben uttered:
[...]
> Make sure the "problem" database isn't configured for auto-close, also
> make sure it's not the target for log-shipping, or some other process
> that might take it offline to do a restore, etc.
Hi Tracy et al,
Thanks for all your advice; so far it looks like the auto-close 'feature'
was most likely the root of my problems (or at least a major factor...);
the system seems to be performing rather more reliably with that disabled.
Cheers,
Alex.|||Alex Craven wrote:
> On Fri, 29 Sep 2006 08:14:30 -0500, Tracy McKibben uttered:
> [...]
>> Make sure the "problem" database isn't configured for auto-close, also
>> make sure it's not the target for log-shipping, or some other process
>> that might take it offline to do a restore, etc.
>
> Hi Tracy et al,
> Thanks for all your advice; so far it looks like the auto-close 'feature'
> was most likely the root of my problems (or at least a major factor...);
> the system seems to be performing rather more reliably with that disabled.
> Cheers,
> Alex.
>
That auto-close option should NEVER be enabled for a production
database. Another one to watch out for is auto-shrink.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Intermittent connection dramas: "Cannot open user default database..."

Hi,
I've been experiencing some very strange, intermittent failures connecting
to an MS SQL database... basically at seemingly random times the server
decides that it can't open the requested database (reporting error 4062:
"Cannot open user default database. Using master database instead"). I've
so far been unable to track down any useful background to this error
message; has anyone on this group come across similar problems? Any clues
as to what might be triggering this sort of fault? More specific details
follow:
* In the majority of cases, connection is established correctly (albeit
sometimes a little slowly).
* Connections are being made to the local machine, using straightforward,
static code. The problem isn't something trivial like a mis-typed
database name, incorrect password etc. Re-running the SAME CODE will
eventually succeed.
* The server isn't particularly heavily loaded, and I can perceive no
pattern to the fault (it seems no more prevalent when the
server's busy, nor after any particular events).
* Nothing relevant is documented in the Event Viewer, nor the server logs.
Am I looking in the right place'
* We're running SQL Server 2005 Express Edition, latest service pack...
are there any connection rate limitations on this software? All
documentation I've seen indicates that there are not, but this behaviour
almost suggests that there may be...
* Connecting (from local machine) using php mssql module (latest stable
version).
* Although possibly symptomatic of a DoS, there's no other evidence to
suggest that this is the case (TCP to the service is firewalled).
Would be grateful for any advice on this issue.
Cheers,
Alex.Alex Craven wrote:
> Hi,
> I've been experiencing some very strange, intermittent failures connecting
> to an MS SQL database... basically at seemingly random times the server
> decides that it can't open the requested database (reporting error 4062:
> "Cannot open user default database. Using master database instead"). I've
> so far been unable to track down any useful background to this error
> message; has anyone on this group come across similar problems? Any clues
> as to what might be triggering this sort of fault? More specific details
> follow:
> * In the majority of cases, connection is established correctly (albeit
> sometimes a little slowly).
> * Connections are being made to the local machine, using straightforward,
> static code. The problem isn't something trivial like a mis-typed
> database name, incorrect password etc. Re-running the SAME CODE will
> eventually succeed.
> * The server isn't particularly heavily loaded, and I can perceive no
> pattern to the fault (it seems no more prevalent when the
> server's busy, nor after any particular events).
> * Nothing relevant is documented in the Event Viewer, nor the server logs.
> Am I looking in the right place'
> * We're running SQL Server 2005 Express Edition, latest service pack...
> are there any connection rate limitations on this software? All
> documentation I've seen indicates that there are not, but this behaviour
> almost suggests that there may be...
> * Connecting (from local machine) using php mssql module (latest stable
> version).
> * Although possibly symptomatic of a DoS, there's no other evidence to
> suggest that this is the case (TCP to the service is firewalled).
>
> Would be grateful for any advice on this issue.
> Cheers,
> Alex.
>
>
Make sure the "problem" database isn't configured for auto-close, also
make sure it's not the target for log-shipping, or some other process
that might take it offline to do a restore, etc.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Do you periodically rename, remove, or detach the database?
Do you have any jobs that put the database into single-user mode or
otherwise alter the availability of the database?|||On Fri, 29 Sep 2006 08:14:30 -0500, Tracy McKibben uttered:
[...]
> Make sure the "problem" database isn't configured for auto-close, also
> make sure it's not the target for log-shipping, or some other process
> that might take it offline to do a restore, etc.
Hi Tracy et al,
Thanks for all your advice; so far it looks like the auto-close 'feature'
was most likely the root of my problems (or at least a major factor...);
the system seems to be performing rather more reliably with that disabled.
Cheers,
Alex.|||Alex Craven wrote:
> On Fri, 29 Sep 2006 08:14:30 -0500, Tracy McKibben uttered:
> [...]
>
> Hi Tracy et al,
> Thanks for all your advice; so far it looks like the auto-close 'feature'
> was most likely the root of my problems (or at least a major factor...);
> the system seems to be performing rather more reliably with that disabled.
> Cheers,
> Alex.
>
That auto-close option should NEVER be enabled for a production
database. Another one to watch out for is auto-shrink.
Tracy McKibben
MCDBA
http://www.realsqlguy.com