How to diagnose SqlConnection Leaks


This is a fairly common problem – you test your app, everything works great. Throw it into production and then it stops working and you see a lot of these:

System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.

You then check the number of connections in Perfmon and see that you have 100 connections used up…oops…something is not closing the connection….

You can follow the instructions on this blog to figure out which pool you’re leaking from, but sometimes that’s not quite enough, and you have to figure out where exactly in the code the connection is getting leaked. Here’s how:

First, dump all the SqlConnection objects in the heap:

To do that more efficiently, I use !DumpHeap -MT <MethodTable> but I have to resolve the method table for SqlConnection like so:

0:108> !Name2EE System.Data.dll System.Data.SqlClient.SqlConnection
Module:      000007fee4db1000
Assembly:    System.Data.dll
Token:       0000000002000281
MethodTable: 000007fee4f96170
EEClass:     000007fee4dd8110
Name:        System.Data.SqlClient.SqlConnection

Then, use this little debugger command to dump all those connections (you might want to use a log file by doing “.logopen mylogfile.txt”):

0:108> .foreach (foo {!DumpHeap -short -MT 000007fee4f96170}) {.echo foo; !do foo}

00000001000ce1a8

Name:        System.Data.SqlClient.SqlConnection
MethodTable: 000007fee4f96170
EEClass:     000007fee4dd8110
Size:        128(0x80) bytes
File:        D:WindowsMicrosoft.NetassemblyGAC_64System.Datav4.0_4.0.0.0__b77a5c561934e089System.Data.dll
Fields:
              MT    Field   Offset                 Type VT     Attr            Value Name
000007fee3ec3c38  4000198        8        System.Object  0 instance 0000000000000000 __identity
000007fee5a5f2e8  40002d2       10 …ponentModel.ISite  0 instance 0000000000000000 site
000007fee5a6dbe0  40002d3       18 ….EventHandlerList  0 instance 0000000100dc3ea0 events
000007fee3ec3c38  40002d1      1b8        System.Object  0   shared           static EventDisposed
                                 >> Domain:Value  0000000001770530:NotInit  000000000fe9e740:00000003ffe0a4b0 <<
000007fee4fb1510  40002c0       20 …hangeEventHandler  0 instance 0000000000000000 _stateChangeEventHandler
000007fee4fae4c0  400107c       28 …t.SqlDebugContext  0 instance 0000000000000000 _sdc
000007fee3ec4f30  400107d       70       System.Boolean  1 instance                0 _AsyncCommandInProgress
000007fee4fb63e0  400107e       30 …ent.SqlStatistics  0 instance 0000000000000000 _statistics
000007fee3ec4f30  400107f       71       System.Boolean  1 instance                0 _collectstats
000007fee3ec4f30  4001080       72       System.Boolean  1 instance                0 _fireInfoMessageEventOnUserErrors
000007fee553de48  4001081       38 ….Task, mscorlib]]  0 instance 0000000000000000 _currentCompletion
000007fee4f998b0  4001082       40 …ent.SqlCredential  0 instance 0000000000000000 _credential
000007fee3ec3658  4001083       48        System.String  0 instance 0000000100dc2a10 _connectionString
000007fee4f9c0e0  4001086       50 …ConnectionOptions  0 instance 0000000000000000 _userConnectionOptions
000007fee4f9df40  4001087       58 …nnectionPoolGroup  0 instance 0000000000000000 _poolGroup
000007fee4f9d730  4001088       60 …onnectionInternal  0 instance 00000003ffdf82e0 _innerConnection
000007fee3ec6408  4001089       68         System.Int32  1 instance                1 _closeCount
000007fee3ec6408  400108b       6c         System.Int32  1 instance            75433 ObjectID

00000004c0710338

Name:        System.Data.SqlClient.SqlConnection
MethodTable: 000007fee4f96170
EEClass:     000007fee4dd8110
Size:        128(0x80) bytes
File:        D:WindowsMicrosoft.NetassemblyGAC_64System.Datav4.0_4.0.0.0__b77a5c561934e089System.Data.dll
Fields:
              MT    Field   Offset                 Type VT     Attr            Value Name
000007fee3ec3c38  4000198        8        System.Object  0 instance 0000000000000000 __identity
000007fee5a5f2e8  40002d2       10 …ponentModel.ISite  0 instance 0000000000000000 site
000007fee5a6dbe0  40002d3       18 ….EventHandlerList  0 instance 00000004c0711d08 events
000007fee3ec3c38  40002d1      1b8        System.Object  0   shared           static EventDisposed
                                 >> Domain:Value  0000000001770530:NotInit  000000000fe9e740:00000003ffe0a4b0 <<
000007fee4fb1510  40002c0       20 …hangeEventHandler  0 instance 0000000000000000 _stateChangeEventHandler
000007fee4fae4c0  400107c       28 …t.SqlDebugContext  0 instance 0000000000000000 _sdc
000007fee3ec4f30  400107d       70       System.Boolean  1 instance                0 _AsyncCommandInProgress
000007fee4fb63e0  400107e       30 …ent.SqlStatistics  0 instance 0000000000000000 _statistics
000007fee3ec4f30  400107f       71       System.Boolean  1 instance                0 _collectstats
000007fee3ec4f30  4001080       72       System.Boolean  1 instance                0 _fireInfoMessageEventOnUserErrors
000007fee553de48  4001081       38 ….Task, mscorlib]]  0 instance 0000000000000000 _currentCompletion
000007fee4f998b0  4001082       40 …ent.SqlCredential  0 instance 0000000000000000 _credential
000007fee3ec3658  4001083       48        System.String  0 instance 00000004c0710270 _connectionString
000007fee4f9c0e0  4001086       50 …ConnectionOptions  0 instance 000000023fdf8250 _userConnectionOptions
000007fee4f9df40  4001087       58 …nnectionPoolGroup  0 instance 000000023fdf8578 _poolGroup
000007fee4f9d730  4001088       60 …onnectionInternal  0 instance 0000000400005d30 _innerConnection
000007fee3ec6408  4001089       68         System.Int32  1 instance                0 _closeCount
000007fee3ec6408  400108b       6c         System.Int32  1 instance            74597 ObjectID
000007fee3ec3c38  400107b      ea0        System.Object  0   shared           static EventInfoMessage

<…>

You will notice this last connection object is still open (_closeCount is zero!). Now let’s make sure it’s unreachable (i.e. leaked!).

0:108> !GCRoot 00000004c0710338
Found 0 unique roots (run ‘!GCRoot -all’ to see all roots).

Ok, so it’s unreachable and open, it’s therefore a leaked connection – now let’s see if we can tell more than just the connection string by dumping the inner connection (the outer is just a shell):

0:108> !do  0000000400005d30
Name:        System.Data.SqlClient.SqlInternalConnectionTds
MethodTable: 000007fee4f9e8a8
EEClass:     000007fee4e00cd0
Size:        296(0x128) bytes
File:        D:WindowsMicrosoft.NetassemblyGAC_64System.Datav4.0_4.0.0.0__b77a5c561934e089System.Data.dll
Fields:
              MT    Field   Offset                 Type VT     Attr            Value Name
000007fee3ec6408  40008b8       38         System.Int32  1 instance              280 _objectID
000007fee3ec4f30  40008bb       44       System.Boolean  1 instance                0 _allowSetConnectionString
000007fee3ec4f30  40008bc       45       System.Boolean  1 instance                1 _hidePassword
000007fee4f97c68  40008bd       3c         System.Int32  1 instance                1 _state
000007fee3ec0850  40008be        8 System.WeakReference  0 instance 0000000400005ec8 _owningObject
000007fee4f9bfc8  40008bf       10 ….DbConnectionPool  0 instance 000000023fdf8950 _connectionPool
000007fee4f98ca8  40008c0       18 …ctionPoolCounters  0 instance 000000047ff5dc88 _performanceCounters
000007fee4f9a6c8  40008c1       20 …ferenceCollection  0 instance 000000040002bbe0 _referenceCollection
000007fee3ec6408  40008c2       40         System.Int32  1 instance                0 _pooledCount
000007fee3ec4f30  40008c3       46       System.Boolean  1 instance                0 _connectionIsDoomed
000007fee3ec4f30  40008c4       47       System.Boolean  1 instance                0 _cannotBePooled
000007fee3ec4f30  40008c5       48       System.Boolean  1 instance                0 _isInStasis
000007fee3ec0980  40008c6       50      System.DateTime  1 instance 0000000400005d80 _createTime
000007fef08f39d8  40008c7       28 …tions.Transaction  0 instance 0000000000000000 _enlistedTransaction
000007fef08f39d8  40008c8       30 …tions.Transaction  0 instance 0000000000000000 _enlistedTransactionOriginal
000007fee3ec6408  40008b7      6e0         System.Int32  1   shared           static _objectTypeCount
                                 >> Domain:Value  0000000001770530:NotInit  000000000fe9e740:353 <<
000007fee4f9f5d8  40008b9      618 …teChangeEventArgs  0   shared           static StateChangeClosed
                                 >> Domain:Value  0000000001770530:NotInit  000000000fe9e740:000000047ff62d98 <<
000007fee4f9f5d8  40008ba      620 …teChangeEventArgs  0   shared           static StateChangeOpen
                                 >> Domain:Value  0000000001770530:NotInit  000000000fe9e740:000000047ff62db0 <<
000007fee4f9ae10  4001258       58 …lConnectionString  0 instance 000000023fdf8250 _connectionOptions
000007fee3ec4f30  4001259       88       System.Boolean  1 instance                0 _isEnlistedInTransaction
000007fee3ec77d0  400125a       60        System.Byte[]  0 instance 0000000000000000 _promotedDTCToken
000007fee3ec77d0  400125b       68        System.Byte[]  0 instance 0000000000000000 _whereAbouts
000007fee3ec3658  400125c       70        System.String  0 instance 0000000400027c20 <CurrentDatabase>k__BackingField
000007fee3ec3658  400125d       78        System.String  0 instance 000000023fdf8368 <CurrentDataSource>k__BackingField
000007fee5552358  400125e       80 …egatedTransaction  0 instance 0000000000000000 <DelegatedTransaction>k__BackingField
000007fee4f9ccc0  400126e       90 …GroupProviderInfo  0 instance 000000023fdf85c0 _poolGroupProviderInfo
000007fee4f99ad8  400126f       98 …lClient.TdsParser  0 instance 0000000400005fb0 _parser
000007fee4f9dbb0  4001270       a0 …lient.SqlLoginAck  0 instance 0000000400027ca8 _loginAck
000007fee4f998b0  4001271       a8 …ent.SqlCredential  0 instance 0000000000000000 _credential
[…]

Now let’s check the TDS Parser (TDS = Tabular Data Stream) – to get the actual data:

0:108> !do 0000000400005fb0
Name:        System.Data.SqlClient.TdsParser
MethodTable: 000007fee4f99ad8
EEClass:     000007fee4dff318
Size:        160(0xa0) bytes
File:        D:WindowsMicrosoft.NetassemblyGAC_64System.Datav4.0_4.0.0.0__b77a5c561934e089System.Data.dll
Fields:
              MT    Field   Offset                 Type VT     Attr            Value Name
000007fee3ec6408  40014d6       70         System.Int32  1 instance              275 _objectID
000007fee4f9dcc0  40014d8        8 …ParserStateObject  0 instance 0000000400006050 _physicalStateObj
000007fee4f9dcc0  40014d9       10 …ParserStateObject  0 instance 0000000000000000 _pMarsPhysicalConObj
000007fee4fb9758  40014da       74         System.Int32  1 instance                2 _state
000007fee3ec3658  40014db       18        System.String  0 instance 000000023fdf8368 _server
000007fee3ec4f30  40014dc       88       System.Boolean  1 instance                0 _fResetConnection
000007fee3ec4f30  40014dd       89       System.Boolean  1 instance                0 _fPreserveTransaction
[…]

We then have to look at the TDS Parser State Object:

0:108> !do 0000000400006050
Name:        System.Data.SqlClient.TdsParserStateObject
MethodTable: 000007fee4f9dcc0
EEClass:     000007fee4e009b8
Size:        424(0x1a8) bytes
File:        D:WindowsMicrosoft.NetassemblyGAC_64System.Datav4.0_4.0.0.0__b77a5c561934e089System.Data.dll
Fields:
              MT    Field   Offset                 Type VT     Attr            Value Name
000007fee3ec6408  40015a4      128         System.Int32  1 instance              275 _objectID
000007fee4f99ad8  40015a5        8 …lClient.TdsParser  0 instance 0000000400005fb0 _parser
000007fee4f99528  40015a6       10 …lClient.SNIHandle  0 instance 0000000400027ab8 _sessionHandle
000007fee3ec0850  40015a7       18 System.WeakReference  0 instance 00000004000061f8 _owner
000007fee3ec6408  40015a8      12c         System.Int32  1 instance                0 _activateCount
000007fee3ec6408  40015a9      130         System.Int32  1 instance                8 _inputHeaderLen
000007fee3ec6408  40015aa      134         System.Int32  1 instance                8 _outputHeaderLen
000007fee3ec77d0  40015ab       20        System.Byte[]  0 instance 0000000400029c68 _outBuff
000007fee3ec6408  40015ac      138         System.Int32  1 instance                8 _outBytesUsed
000007fee3ec77d0  40015ad       28        System.Byte[]  0 instance 0000000400027d10 _inBuff
000007fee3ec6408  40015ae      13c         System.Int32  1 instance             5084 _inBytesUsed
000007fee3ec6408  40015af      140         System.Int32  1 instance             5084 _inBytesRead
000007fee3ec6408  40015b0      144         System.Int32  1 instance                0 _inBytesPacket
000007fee3ec5ab8  40015b1      170          System.Byte  1 instance                3 _outputMessageType
000007fee3ec5ab8  40015b2      171          System.Byte  1 instance                1 _messageStatus
000007fee3ec5ab8  40015b3      172          System.Byte  1 instance                1 _outputPacketNumber
000007fee3ec4f30  40015b4      173       System.Boolean  1 instance                0 _pendingData

[…]

_outBuff is our output buffer, i.e. what we sent to the server, and it has our command text… Now I know who’s leaking!

0:108> !do 0000000400029c68
Name:        System.Byte[]
MethodTable: 000007fee3ec77d0
EEClass:     000007fee38ca4b8
Size:        8024(0x1f58) bytes
Array:       Rank 1, Number of elements 8000, Type Byte
Content:     …………………………..


Comments (2)

  1. VJ says:

    Really useful and make lot of sense than using third-party solution for.

  2. Hello, this really very useful, the only updated source of information I found on this topic. I have a question thought, I'm trying to identify a connection leak in a .NET 4 production process that I don't have the source code.

    I generated a dump of the process and I'm inspecting all the SqlConnection objects, which have their '_closecount' value in 1 (all of them). Is this enough to assert that none of the connections are leaked?

    It is strange that I'm seeing the NumberOfReclaimedConnections counter rise, but all the SqlConnections on the heap seems to be closed.

    Thanks in advance!

    Sebastian Durandeu

Skip to main content