One of my colleagues, Kamil Sykora, compiled a document that goes through how to troubleshoot leaked SqlConnection objects (from a .NET 2.0 perspective). It was a fairly large document, so I’m not going to post the whole thing. I’m going to split it out over several posts and base the examples off of a custom demo that I have created.
A common issue that we often observe is "leaking" connections in a .NET application. While leaking objects is technically not possible in a .NET application, the issue that we often observe is that customers are not closing SqlConnection objects before they go out of scope. This results in unused SqlConnection objects holding on to internal references and native objects until these SqlConnection objects get collected by the Garbage Collector.
The most common symptom of this is this error message:
Exception type: System.InvalidOperationException
Message: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
SP IP Function
0636F4B8 653CF486 System_Data_ni!System.Data.ProviderBase.DbConnectionFactory.GetConnection(System.Data.Common.DbConnection)+0x133f46
0636F4C4 652D69BA System_Data_ni!System.Data.ProviderBase.DbConnectionClosed.OpenConnection(System.Data.Common.DbConnection, System.Data.ProviderBase.DbConnectionFactory)+0x6a
0636F4F8 652F5440 System_Data_ni!System.Data.SqlClient.SqlConnection.Open()+0x70
The steps to take when we see this exception are:
Find out how the customer is opening and closing connections and ensure that they are explicitly closing them in all cases. If doing this is not sufficient and it’s not 100% clear if all connections are getting closed.
Obtain a user dump of the process once the issue occurs. We can obtain a hang dump as soon as the exception occurs (good) or a crash dump on the exception (better).
Follow the debugging steps in this series to confirm if there are any unreferenced connections that are still holding on to internal references.
The following debugging instructions are based on an x86 user dump. Similar steps can be taken for a 64-bit dump as noted below.
For the dumps, we used the SOS debugging extension which ships with the .NET Framework. You can load the extension in the debugger by using the following command:
0:000> .loadby sos mscorwks
Locating the pool(s)
First we find all the pool object method tables in the process.
Then we dump out the individual pool objects. In this case there are a total of one pool. We dump out the pool and look for the _totalObjects member variable to see how many objects we have in that pool. Note that in the below case we have at least one pool with 100 connections which is the default number of maximum connections in a pool. We also look at the _connectionPoolGroupOptions variable and dump it out to double-check that the _maxPoolSize has been reached.
0:000> !dumpheap -mt 653fff4c
Address MT Size
012bbe80 653fff4c 100
total 1 objects
MT Count TotalSize Class Name
653fff4c 1 100 System.Data.ProviderBase.DbConnectionPool
Total 1 objects
At this point we have found that our pool has 100 connections whose max pool size is 100. This means that any connection requests to this pool will return the above mentioned error message. This is the immediate cause of the error message and we do not have to spend time looking for other potential causes, such as physical connectivity problems etc.
Next time, we will go into the internal connection object.
Adam W. Saxton | Microsoft SQL Server Escalation Services