Solution: Forcibly Closed SQL Server Connections

| 5 Comments | No TrackBacks

Having seen the litany of non-answers (and random guesses) on the SqlClient.SQLException:

"A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"

I decided to figure this out for myself. No, it has nothing to do with named pipes or TCP/IP protocols or using wireless connections or VPNs--it has to do with the SQL Server instance configuration. And no, you don't have to disable connection pooling. Frankly, I would be surprised if lack of a connection pool would fix the problem.

This is not a new problem. Consider that when the SQL Server is accessed by a client that wants to open another connection the server instance must validate the credentials and make sure that the connection is valid in a number of ways. Once that's done, the SQL Server instance then tries to add the connection to the already existing connections its managing--it's own internal pool of active connections. Ordinarily (and by default), SQL Server instances can accept any number of connections. In a practical sense, this is not always possible so the DBA might set the number of connections on the instance to some value--N. Once N connections have been made the next connection attempt might get through the login layer but the server simply cuts it off at the knees and forcibly closes it. Hopefully, this does not pooch the client-side connection pool--that remains to be seen (still testing that) but it does throw the exception mentioned above.

We still see this error when testing with a connection limit cap (even a big one like 100 connections) when using Visual Studio doing iterative tests. That's because if your code does not close the connection(s) it opens during a test run, when you start a new run you get a new process id and a new connection pool but the old pool is owned by Visual Studio and the connections remain. Don't believe me? Turn on the Perfmon tool and monitor the SQLClient connection pool counters and see... open some connections and kill the application without closing the connections. Note that the number of pools and pooled connections don't go down until you exit Visual Studio.

The solution? Either trap the exception and try again or take your DBA some candy and flowers and get him (or her) to bump up the number or set the number of connections to "0" which says to not limit the number of connections. This is done in SSMS and should only be necessary if someone has been playing with the setting. Just remember to restart the SQL Server instance service after making this change—SSMS won’t remind you to do so.

 

hth

No TrackBacks

TrackBack URL: http://betav.com/blogadmin/mt-tb.cgi/2334

5 Comments

Does disabling the vshost (Visual Studio hosting) from the project properties debug tab help address release of the pools ?

This is an excellent and simple solution. Thanks a lot! Been searching a way out for this problem for a very long time! Really appreciate it :)

Hello,
I installed this on my laptop and I cant even log into SSMS to change the number of connections,so what do I do next?

Reboot the server and restart in single-user mode. This gives you exclusive access to the server. Set the Connections limit and restart in normal mode. It should fix the problem...

Excellent Post.. didnt think about this. Tried the steps in other posts didnt resolve the issue. Hope this resolves. :)

Thanks

Pages

Powered by Movable Type 4.21-en

About this Entry

This page contains a single entry by William Vaughn published on November 30, 2008 5:21 PM.

Windows Server 2008 Backup—Not Quite Done was the previous entry in this blog.

DevTeach Vancouver Fire Sale! is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.