Have you ever had a complaint that the application keeps crashing or timing out with some kind of error relating to the fact that it can’t connect to sql server due to the maximum number of connections being reached ? They reset the app pool and things started working again….until it didn’t! You have had confirmation that there is no memory leak within the application so therefore it must be sql server that is causing the problem. Hmmm.
By deafult SQL server allows a lot of connections – 32767 to be precise. You can check out the configured value of your server by running
select @@MAX_CONNECTIONS
which if not changed from the default, should return the value 32767.
If you check within SSMS you will see it is set to 0 which is the maximum or unlimited (32767)
What we are going to do next is simulate an application that keeps connecting to sql server without releasing any of its connections. For this, I have created a connection string using LINQPad (download here). Now, the default max connection size for a .net application is 100 if not otherwise stated in the connection string.
Here is the code I used to create the connection to my sql server. You simply hit the ‘play’ button within LINQPad and it will try to connect to the database.
click image to view.
Eventually after a number of seconds you should see this error:
click image to view
Now this error may be very similar to the error that your customers are complaining about.
If you run the following TSQL code (courtesy of Glen Berry’s sql2014 diagnostics scripts)
script can be downloaded here…connection-count
click image to view
then you will see that the 100 connections from .net client has been reached. This proves it is nothing to do with sql server !!
Let’s edit our connection string to allow more connections; 250 in this case. Notice though that the loop only allows upto 200 connections so it never reaches its maximum amount of 250, so therefore should not produce the error we saw earlier…
click image to view
.
and if we run our query against the server to see how many connections we now see the 200 connections that the connection string script allowed and no error…
I had to run the  connection in LINKPad 2 times in order to reach the 200. For some reason on the first run it created only 136 connections – no idea why.
I don’t think I have seen a situation where sql server reaches its allowed number of maximum connections if the default setting has never been altered !!!
sqlnights.com 