How to trace connection leak
Problem description: (http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/10/13/connection-leak-its-symptoms-and-troubleshooting.aspx)
Connection leak basically happens when we open a connection to the database from our application and forget to close it, or for some reasons it doesn’t get closed.
Errors related to Connection leak (http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/10/13/connection-leak-its-symptoms-and-troubleshooting.aspx)
1) 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.
2) SQL Server Does Not Exist Or Access Denied
3) General Network Error
Problem confirm:
For .Net 2.0 and above - Use performance counter : .Net data provider for sqlserver -> NumberofreclaimedConnection
For .Net 1.x - http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/10/13/connection-leak-its-symptoms-and-troubleshooting.aspx
Resolution 1: SQL Profiler
Audit Login Event
DatabaseName
LoginSid :Each SID is unique for each login in the server.
EventSubClass: check connection is pooled or not
Audit Logout Event
LoginSid :Each SID is unique for each login in the server.
RPC:Completed Event(The RPC:Completed event class indicates that a remote procedure call has been completed.)
LoginSid :Each SID is unique for each login in the server.
TextData :Text of the stored procedure call.
Step to trace:
- Setup/turn on sql profiler
- Run regression/stress test
- Stop sql profiler
- Retrive sql profiler report
- Find out what is the isolate loginsid (only login but not logout)
- Find out the sql text base on loginsid & Textdata
Resolution 2: C# sqlconnection event - connection.StateChange and System.Diagnostics.StackTrace
CODE refer to http://www.codeproject.com/KB/database/connectionmonitor.aspx
Step to trace:
- Add StateChange Event to SqlConnection
- DoOpen():If connection.open then record the time, connection hashcode, getstacktrace => log
- DoClose():If connection.close then record the time, connection hashcode => log
- Run regresstion/stress test
- Find out what connection lose close