Re: Unable to reconnect to SQL Server after the server is restarted

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

.

Relevant Pages

  • Re: Random database connectivity failure
    ... We can’t find any problems with the pooling, that it uses to many resources. ... pointing to its not a pool problem. ... The server is only used for this web-application, ... white paper on handling the connection pool on my website. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Problem with VPN setup
    ... I used unassigned addresses to create a static pool. ... if I am using RRAS for VPN it would not be many anyway. ... You can see the connection under "Remote Access ... create a VPN server. ...
    (microsoft.public.windows.server.general)
  • Re: Connection Pool Issue
    ... Max pool size 10 seems low... ... My first thought would be that connection poolling is actually disabled on A ... Server A and Server B both are running Windows 2000 Server sp4, ... all the usual places for enabling connection pooling. ...
    (microsoft.public.dotnet.framework.aspnet.webservices)
  • Re: Internal connection fatal error
    ... could spend quite a bit of time working on streamlining your server, ... I also do a perf talk at the Connections conference that discusses how to ... >> 80 connections in the pool tells me this is a heavily loaded system. ... >> Yes, if an operation fails at the server, the connection can be broken. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: VPN error 628
    ... Note that you need a hotfix to enable a L2TP VPN connection over a NAT ... connection (as well as a server that supports L2TP NAT-Traversal). ... is so that it doesn't try and verify the checksums ...
    (microsoft.public.windowsxp.work_remotely)