Db connection errors on a production server

We get the followIng exception message at least 3 times per week on a production server for an asp.net website that has been live for 2 years. Although the db server is solid and never goes offline. Not sure how to solve this. Causes our users to lose their sessions and any unsaved work.

A network-related or instance-specific error occurred while establishing 开发者_高级运维a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - Only one usage of each socket address (protocol/network address/port) is normally permitted.

The clue is to read further into the error message and notice the following:

(provider: TCP Provider, error: 0 - Only one usage of each socket address (protocol/network address/port) is normally permitted.)

The most common reason for this is that the application is NOT using connection pooling. You all know how pooling works, but if you do not, the short version is that opening and closing connections is expensive. So when you use connection pooling and the connection is closed, it is not really closed. Instead it is put into a pool and when a new connection is opened, a new connection is not created, instead it is picked from the pool and 'reactivated'.

Now, if you do not use connection pooling, then a new connection is created and when it is closed, it is disposed and not put into the pool. Why is this important? Well, when a connection is opened a socket (or port) is opened on the client for communication with the server. Now, when the connection is closed, the port is not closed, instead it goes into what is called TIME_WAIT in order to wait for potentially late or misrouted packages etc. The default for the port to be in TIME_WAIT is 240 seconds (4 minutes), and during that time, the port is not available. This means that a new connection can’t use this port during the TIME_WAIT state, and a new one will be opened.

If you are running your application on a Windows 2003 install, the ports that can be used are 1024 to 5000, which means that roughly 4000 ports available. See more at: "TCP TIME-WAIT Delay" http://msdn2.microsoft.com/en-us/library/ms819739.aspx

So, how does this tie together? In the scenario above, imagine that you have 1000 employees or users, and they all come back from lunch, they sit all down and they use the web application in order to bring up 4 records each. The retrieval of each record takes 1 second. Now, if you use the information above and do the math you will understand that 1000 users * 4 connections will open up 4000 ports on the machine that runs the web application, which is the limit. Now all these ports will be in the TIME_WAIT state for 4 minutes and during this time, no new connections can’t be opened and you will get the error above. After the 4 minutes have passed the ports are released and the users can once again connect.

However, if using connection pooling, this will not occur since the connection is not closed and therefore it will not put the port into TIME_WAIT.

On a .Net application, pooling should be ON by default, but it could have been disabled (or set to false) in the connection string. So, simply have a look at the connection string and change it accordingly:

// Pooling false = fails
string cs = @"Data Source=tcp:<your server>;Integrated Security=True;Pooling=false";
// Pooling true = works, this is the default but included here for clarity
string cs = @"Data Source=tcp:<your server>;Integrated Security=True;Pooling=true";

Read Michael's blog on MSDN Blogs.