Re: SQL_Server_does_not_exist_or_access_denied

From: Frank Lehmann (f.leh294_at_world4net.com)
Date: 02/19/04 

Date: Thu, 19 Feb 2004 18:41:12 +0100

Hi Brian,

The connection may have got lost because the client machine opens
a new TCP port with every access to the SQL Server without
re-using the connections/ports previously opened. (TCP port is not
equivalent to SQL Server connection.)

You can watch this behaviour with the netstat service program run in a
DOS window. Connections not being re-used get into the TIME_WAIT state
and will be automatically deleted/released after 4 minutes, unless the TCP
settings are re-configured.

According to how frequently an application accesses the server, it might
happen that you get thousands of TIME_WAIT connections in a relatively short
period of time, until the resources are dead.

This error occurs sporadically, depending on how much TCP ports
are open/released again on the client machine at a given moment.
It results in errors like
[DBNETLIB]SQL server does not exist or access denied
[DBNETLIB]General network error, and a few others.

To mitigate this, you could introduce a second ADO.Connection in
critical places, so that connection pooling works or rather works at all.
Critical place is, for instance, if you open a recordset and before closing it,
you open a second recordset via the same ADO.Connection. Take a
different one.
By this measure, I was able to reduce the number of open TCP ports
from 1000 to 3 in my application.

Other measures are Registry TCP settings:
   MaxUserPort:
   Increase the number of TCP ports to allow more open ports.

   TcpTimedWaitDelay:
   Decrease the number of seconds, until a TCP port is released (down to 30).

Hope, my German English was sufficient to make myself understood.

Regards,
Frank

"Brian Burgess" <bburgess66@hotmail.com> schrieb im Newsbeitrag
news:OmjCpMp9DHA.2756@TK2MSFTNGP10.phx.gbl...
> Hi all,
>
> Apologies for the cross-post. Just wasn't sure where to put this one:
>
> I'm getting this error sporadically on an ADO connection. When it happens
> I also cannot connect through the Enterprise Manager (or anything else!).
> The environment is SQL Server 2000 running on Windows 2000 Advanced Server
> with 512 Meg of RAM. It SEEMS to be running low on resources. Though
> CPU Usage (showing through TaskManager) rarely goes above 2%.
>
> Anyone have any ideas?
>
> Thanks in advance...
>
> -BB
>
>

Relevant Pages
Quantcast