How to troubleshoot leaked SqlConnection objects (.NET 2.0) - Part 1

[mssql-support]

First published on MSDN on Feb 05, 2009

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:

• 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.

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:WINNTassemblyGAC_32System.Data2.0.0.0__b77a5c561934e089System.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:WINNTassemblyGAC_32System.Data2.0.0.0__b77a5c561934e089System.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