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. InnerException: <none> StackTrace (generated): 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:
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.
0:000> !dumpheap -stat -type DbConnectionPool total 26 objects Statistics: MT Count TotalSize Class Name 65404260 1 16 System.Data.ProviderBase.DbConnectionPoolIdentity 65436c90 1 24 System.Collections.Generic.List`1[[System.Data.ProviderBase.DbConnectionPool, System.Data]] 65436598 1 24 System.Collections.Generic.List`1[[System.Data.ProviderBase.DbConnectionPoolGroup, System.Data]] 6540444c 2 24 System.Data.ProviderBase.DbConnectionPool+DbConnectionInternalListStack 65400c70 1 32 System.Data.ProviderBase.DbConnectionPoolGroupOptions 654000a4 1 40 System.Data.ProviderBase.DbConnectionPoolGroup 6543397c 1 52 System.Collections.Generic.Dictionary`2[[System.String, mscorlib],[System.Data.ProviderBase.DbConnectionPoolGroup, System.Data]] 654044a8 1 52 System.Data.ProviderBase.DbConnectionPool+PoolWaitHandles 6543085c 1 60 System.Collections.Generic.Dictionary`2+Entry[[System.String, mscorlib],[System.Data.ProviderBase.DbConnectionPoolGroup, System.Data]][] 65404638 1 64 System.Data.ProviderBase.DbConnectionPool+TransactedConnectionPool 653fff4c 1 100 System.Data.ProviderBase.DbConnectionPool 653ffde4 14 168 System.Data.ProviderBase.DbConnectionPoolCounters+Counter Total 26 objects
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 Statistics: MT Count TotalSize Class Name 653fff4c 1 100 System.Data.ProviderBase.DbConnectionPool Total 1 objects
0:000> !do 012bbe80 Name: System.Data.ProviderBase.DbConnectionPool MethodTable: 653fff4c EEClass: 653ffedc Size: 100(0x64) bytes (C:\WINNT\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll) Fields: MT Field Offset Type VT Attr Value Name 79102290 4001517 44 System.Int32 1 instance 220000 _cleanupWait 65404260 4001518 4 ...ctionPoolIdentity 0 instance 012bd960 _identity 6540012c 4001519 8 ...ConnectionFactory 0 instance 01275c34 _connectionFactory 654000a4 400151a c ...nnectionPoolGroup 0 instance 01279e7c _connectionPoolGroup 65400c70 400151b 10 ...nPoolGroupOptions 0 instance 01279e5c _connectionPoolGroupOptions 65426f4c 400151c 14 ...nPoolProviderInfo 0 instance 00000000 _connectionPoolProviderInfo 65426eac 400151d 48 System.Int32 1 instance 1 _state 6540444c 400151e 18 ...InternalListStack 0 instance 012bbee4 _stackOld 6540444c 400151f 1c ...InternalListStack 0 instance 012bbef0 _stackNew 791186fc 4001520 20 ...ding.WaitCallback 0 instance 012bc348 _poolCreateRequest 791087cc 4001521 24 ...Collections.Queue 0 instance 00000000 _deactivateQueue 791186fc 4001522 28 ...ding.WaitCallback 0 instance 00000000 _deactivateCallback 79102290 4001523 4c System.Int32 1 instance 32 _waitCount 654044a8 4001524 2c ...l+PoolWaitHandles 0 instance 012bbf80 _waitHandles 790fdf04 4001525 30 System.Exception 0 instance 00000000 _resError 7910be50 4001526 5c System.Boolean 1 instance 0 _errorOccurred 79102290 4001527 50 System.Int32 1 instance 5000 _errorWait 791127fc 4001528 34 ...m.Threading.Timer 0 instance 00000000 _errorTimer 791127fc 4001529 38 ...m.Threading.Timer 0 instance 012bc4c0 _cleanupTimer 65404638 400152a 3c ...tedConnectionPool 0 instance 012bc16c _transactedConnectionPool 00000000 400152b 40 0 instance 012bbfb4 _objectList 79102290 400152c 54 System.Int32 1 instance 100 _totalObjects 79102290 400152e 58 System.Int32 1 instance 2 _objectID 791080f0 4001516 5fc System.Random 0 static 012bd9c0 _random 79102290 400152d 828 System.Int32 1 static 2 _objectTypeCount
Here is the DbConnectionPoolGroupOptions object that we can get _maxPoolSize from:
0:000> !do 01279e5c Name: System.Data.ProviderBase.DbConnectionPoolGroupOptions MethodTable: 65400c70 EEClass: 6544cb58 Size: 32(0x20) bytes (C:\WINNT\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll) Fields: MT Field Offset Type VT Attr Value Name 7910be50 4001573 10 System.Boolean 1 instance 0 _poolByIdentity 79102290 4001574 4 System.Int32 1 instance 0 _minPoolSize 79102290 4001575 8 System.Int32 1 instance 100 _maxPoolSize 79102290 4001576 c System.Int32 1 instance 15000 _creationTimeout 7911228c 4001577 14 System.TimeSpan 1 instance 01279e70 _loadBalanceTimeout 7910be50 4001578 11 System.Boolean 1 instance 1 _hasTransactionAffinity 7910be50 4001579 12 System.Boolean 1 instance 0 _useDeactivateQueue 7910be50 400157a 13 System.Boolean 1 instance 0 _useLoadBalancing
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
PingBack from http://www.clickandsolve.com/?p=4031
In the last post in this series, we looked at how we can determine that our Connection pool was exhausted.