How to trace connection leak

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:

1. Setup/turn on sql profiler
2. Run regression/stress test
3. Stop sql profiler
4. Retrive sql profiler report
5. Find out what is the isolate loginsid (only login but not logout)
6. 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

 

//.. // When a connection is added to the ConnectionMonitor // it hooks into the connection's state change event connection.StateChange += new StateChangeEventHandler( Connection_StateChange ); //.. //.. void Connection_StateChange(object sender, StateChangeEventArgs e) { if ( ( e.CurrentState & ConnectionState.Open ) != 0 ) { DoOpen(); } if ( e.CurrentState == ConnectionState.Closed ) { DoClose(); } }

 

return string GetStackTrace() { StackTrace trace = new StackTrace( true ); StringBuilder sb = new StringBuilder(); for ( int i = 1; i < trace.FrameCount; i++ ) { StackFrame frame = trace.GetFrame( i ); sb.AppendFormat("at {0}.{1}/r/n", frame.GetMethod().DeclaringType, frame.GetMethod().Name ); } return sb.ToString(); }

 

// Capture the current stack trace so we can figure // out where unclosed connections originated String mStackTrace = GetStackTrace();

 

Step to trace:

1. Add StateChange Event to SqlConnection
2. DoOpen():If connection.open then record the time, connection hashcode, getstacktrace => log
3. DoClose():If connection.close then record the time, connection hashcode => log
4. Run regresstion/stress test
5. Find out what connection lose close