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.

jbellnewsposts
Fri Sep 29 03:16:49 CDT 2006

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

Tracy
Fri Sep 29 08:14:30 CDT 2006

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

Scott
Fri Sep 29 08:45:21 CDT 2006

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?

Alex
Sun Oct 01 02:56:25 CDT 2006

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.

Tracy
Mon Oct 02 07:45:04 CDT 2006

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