Unable to reconnect to SQL Server after the server is restarted

I am using C#, SQL Server 2000 and ADO.NET in my application. The
Database Server and the application are running on seperate physical
machines. If the database server and the application lose connection
(possibly because the server was restarted, or a network failure of some
sort) the application fails to re-connect.

The application will periodically attempt to re-connect to the server.
It seems that I am unable to connect for a period of time after the
server is restarted. I receive the error message "SQL Server is
unavailable or access is denied". I have seen this error message for as
long as 45 minutes after the server has been restarted. Does anybody
have any ideas on the cause of this? I am using connection pooling with
the default connection pool size. Sample code is provided below.
Thanks in advance for your help,

Trevor

SqlConnection conn = new SqlConnection( conString );

if ( waitForConnection && AllowWaitForConnection )
{
// Retry the connection to the database periodically until successful
while ( conn.State != ConnectionState.Open )
{
try
{
conn.Open();
}
catch ( SqlException e )
{
conn.Close();
ThreadSupport.Sleep( connectionRetryInterval );
}
catch ( InvalidOperationException e2 )
{
conn.Close();
ThreadSupport.CheckAbort();
}
}
}
else
{
...
}

William
Mon Jun 27 11:37:41 CDT 2005

When the server is restarted, it has to repair each database it owns and
this can take some time. The length of time depends on the state of the
transaction log and other issues. Another issue you might be experiencing is
the fact that when the server goes down, all of the connections in the pool
are broken off at the server end, but the pooling mechanism does not know
this until you try to open one of its pooled connections. As you open each
connection in the dead pool, the pooler returns an exception and if the
server is not available, it destroys the connectiod in the pool. If you have
50 pooled connections (and you might have twice that or more), you have to
try to open 50 times before you can even attempt to try to open a connection
that will work. The alternative is to restart your application or (far
easier) change your connection string in some subtle way. This will create
another pool that can be used to determine if the server is back up. In
ADO.NET 2.0 you'll (finally and at long last) be able to flush the pool and
start over, but the pooler will also know how to automatically detect bad
connections and do it for you.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Trevor McClean" <tkmcclean@hotmail.com> wrote in message
news:eoBtr1yeFHA.2740@TK2MSFTNGP10.phx.gbl...
>I am using C#, SQL Server 2000 and ADO.NET in my application. The Database
>Server and the application are running on seperate physical machines. If
>the database server and the application lose connection (possibly because
>the server was restarted, or a network failure of some sort) the
>application fails to re-connect.
>
> The application will periodically attempt to re-connect to the server. It
> seems that I am unable to connect for a period of time after the server is
> restarted. I receive the error message "SQL Server is unavailable or
> access is denied". I have seen this error message for as long as 45
> minutes after the server has been restarted. Does anybody have any ideas
> on the cause of this? I am using connection pooling with the default
> connection pool size. Sample code is provided below. Thanks in advance
> for your help,
>
> Trevor
>
> SqlConnection conn = new SqlConnection( conString );
>
> if ( waitForConnection && AllowWaitForConnection )
> {
> // Retry the connection to the database periodically until successful
> while ( conn.State != ConnectionState.Open )
> {
> try
> {
> conn.Open();
> }
> catch ( SqlException e )
> {
> conn.Close();
> ThreadSupport.Sleep( connectionRetryInterval );
> }
> catch ( InvalidOperationException e2 )
> {
> conn.Close();
> ThreadSupport.CheckAbort();
> }
> }
> }
> else
> {
> ...
> }

Trevor
Tue Jun 28 08:45:00 CDT 2005

Thanks for the quick reply, after reading your post yesterday I modified
my testing utility to verify some things. I am not sure I am actually
seeing the problem you described below, so any other ideas would be very
beneficial.

I have written a test that will verify a connection to the database is
still valid every 15 seconds. Each time period (15 seconds) I will
verify the connection object still has an open state (if not I will
attempt to open it) and perform a simple query of the data. Using a
performance monitor I can verify that only 1 connection in the
connection pool is created. I simulate the server disconnecting by
stopping SQL Server service in Service Manager and wait 15 minutes.
Upon restarting the SQL Server service it takes 30+ minutes for my test
utility to re-connect. Again each time I attempt to open the connection
I receive the "Sql Server does not exist or access is denied" error
message.

As an additional test, I used ethereal to sniff the connection between
my test utility and the server (after it was restarted) and I did not
see any traffic between the two machines until the connection was
re-established.

Currently my only thoughts are:
1) Am I not giving enough time for the connection to be removed from the
pool after the framework determines the connection is no longer valid.

2) Have I some how corrupted the connection pool, and after a certain
number of retries the framework will clean up the corrupted pool?

3) If I was to switch to a new connection pool, am I going to be leaking
connection objects? Will garbage collection clean the connections up
properly? The application is intended to run for months at a time
without being restarted.

Any help is much appreciated,

Trevor

William (Bill) Vaughn wrote:
> When the server is restarted, it has to repair each database it owns and
> this can take some time. The length of time depends on the state of the
> transaction log and other issues. Another issue you might be experiencing is
> the fact that when the server goes down, all of the connections in the pool
> are broken off at the server end, but the pooling mechanism does not know
> this until you try to open one of its pooled connections. As you open each
> connection in the dead pool, the pooler returns an exception and if the
> server is not available, it destroys the connectiod in the pool. If you have
> 50 pooled connections (and you might have twice that or more), you have to
> try to open 50 times before you can even attempt to try to open a connection
> that will work. The alternative is to restart your application or (far
> easier) change your connection string in some subtle way. This will create
> another pool that can be used to determine if the server is back up. In
> ADO.NET 2.0 you'll (finally and at long last) be able to flush the pool and
> start over, but the pooler will also know how to automatically detect bad
> connections and do it for you.
>
> hth
>

William
Tue Jun 28 11:35:51 CDT 2005

Sadly, the Connection State property does not change when the server is
disconnected or goes down. That won't happen until ADO.NET 2.0.
I would get the profiler involved. Turn it on and connect it as soon as the
server starts and watch what's happening.
There is nothing in the pooling mechanism to "clean up" except that when a
connection open attempt is made to a pooled connection--if it's bad, it's
discarded. Closed connections are released from the pool after 4-8
minutes--orphaned connections (unclosed) are not released until the "owner"
your appdomain or application ends.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Trevor McClean" <tkmcclean@hotmail.com> wrote in message
news:%23O5gVe%23eFHA.344@TK2MSFTNGP10.phx.gbl...
> Thanks for the quick reply, after reading your post yesterday I modified
> my testing utility to verify some things. I am not sure I am actually
> seeing the problem you described below, so any other ideas would be very
> beneficial.
>
> I have written a test that will verify a connection to the database is
> still valid every 15 seconds. Each time period (15 seconds) I will verify
> the connection object still has an open state (if not I will attempt to
> open it) and perform a simple query of the data. Using a performance
> monitor I can verify that only 1 connection in the connection pool is
> created. I simulate the server disconnecting by stopping SQL Server
> service in Service Manager and wait 15 minutes. Upon restarting the SQL
> Server service it takes 30+ minutes for my test utility to re-connect.
> Again each time I attempt to open the connection I receive the "Sql Server
> does not exist or access is denied" error message.
>
> As an additional test, I used ethereal to sniff the connection between my
> test utility and the server (after it was restarted) and I did not see any
> traffic between the two machines until the connection was re-established.
>
> Currently my only thoughts are:
> 1) Am I not giving enough time for the connection to be removed from the
> pool after the framework determines the connection is no longer valid.
>
> 2) Have I some how corrupted the connection pool, and after a certain
> number of retries the framework will clean up the corrupted pool?
>
> 3) If I was to switch to a new connection pool, am I going to be leaking
> connection objects? Will garbage collection clean the connections up
> properly? The application is intended to run for months at a time without
> being restarted.
>
> Any help is much appreciated,
>
> Trevor
>
> William (Bill) Vaughn wrote:
>> When the server is restarted, it has to repair each database it owns and
>> this can take some time. The length of time depends on the state of the
>> transaction log and other issues. Another issue you might be experiencing
>> is the fact that when the server goes down, all of the connections in the
>> pool are broken off at the server end, but the pooling mechanism does not
>> know this until you try to open one of its pooled connections. As you
>> open each connection in the dead pool, the pooler returns an exception
>> and if the server is not available, it destroys the connectiod in the
>> pool. If you have 50 pooled connections (and you might have twice that or
>> more), you have to try to open 50 times before you can even attempt to
>> try to open a connection that will work. The alternative is to restart
>> your application or (far easier) change your connection string in some
>> subtle way. This will create another pool that can be used to determine
>> if the server is back up. In ADO.NET 2.0 you'll (finally and at long
>> last) be able to flush the pool and start over, but the pooler will also
>> know how to automatically detect bad connections and do it for you.
>>
>> hth
>>