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