Find Leaked Database Connections in ASP.NET Web Applications

• Download source files - 18.9 Kb

Introduction

In this article, I present a ConnectionMonitor class that monitors how long database connections have been open in ASP.NET web applications. The class can also optionally write entries to the Windows event log of all connections that have been opened for a given length of time, including a stack trace of where each connection was first created.

The ConnectionMonitor class was created for SqlConnections, but there is no reason why the code wouldn't work for any other type of connection by simply replacing SqlConnection with your connection type. (If you're using version 2 of .NET, you can replace SqlConnection with DbConnection, the base class from which all connection classes are derived.)

Background

I came up with the idea for this utility when I saw the following error in my web application's event log:

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

After a reboot of the server, the error would not occur for several weeks, then it would begin to appear with gradually increasing frequency.

There are several reasons why this can happen, but the most likely cause is that connections are being "leaked." That is, connections are opened but never closed. (For an excellent discussion on this problem, see here[^]).

One way to "leak" a connection (obviously) is to simply forget to close it. Another slightly less obvious way: if an exception is thrown between the opening and closing of the connection, the connection will be leaked.

SqlConnection cnn = new SqlConnection(myConnectionString); 
cnn.Open();
// ...
// ...do something with the connection
// ... 
// if an exception is thrown while 'doing something' 
// connection will never be closed
// and is "leaked"
cnn.Close();

The fix of course is as follows:

SqlConnection cnn = new SqlConnection(myConnectionString); 
try
{
    cnn.Open();
// ...
// ...do something with the connection
// ... 
}
finally
{
    cnn.Close();
}

Rather than doing a visual code review of every connection being created (I have three huge ASP.NET web applications running!), I decided to come up with a way to monitor all connections that are opened in my applications.

Setting up monitoring

To enable connection monitoring in your application, you must construct a ConnectionMonitor object and store it in the web application's global HttpApplicationState object. The easiest way to do that is to use the ConnectionUtility class that is provided with the sample project:

ConnectionMonitor monitor = ConnectionUtility.Monitor

This static property checks to see if there is already a ConnectionMonitor stored in the HttpApplicationState object. If there isn't, it creates one. Simple, right?

The real difficulty in monitoring connections is that every time you create a connection, you must add it to the ConnectionMonitor as such:

SqlConnection cnn = new SqlConnection();
cnn.ConnectionString= myConnectionString;
ConnectionUtility.Monitor.Add(new ConnectionInfo(cnn));

In my apps, this wasn't much of a problem since I have all of my database access code residing in one small assembly. I only had to change a couple of lines of code. If you're not so fortunate, there's nothing for it but to find all of your connections and add them to the monitor when they are created. This is admittedly a potential impediment to using this tool. To make things a little bit easier, the ConnectionUtility class provides a static helper method which takes care of creating the connection and adding it to the ConnectionMonitor:

SqlConnection cnn = 
   ConnectionUtility.CreateConnection( myConnectionString )

After you've added it to the monitor, you're done. You don't have to worry about when the connection is opened and/or closed as ConnectionMonitor manages that automatically.

Using the ConnectionMonitor

When you use the ConnectionUtility.Monitor class to create your monitor, it automatically constructs an System.Diagnostics.EventLog object that logs entries into the Application event log using a source name of "ConnectionMonitoring". You can view these log entries using the Event Viewer that is included with Windows. A typical entry includes the length of time the connection has been opened.

But of course, this doesn't help you much if you don't know where the connection was created from in your application. So the log entry also includes a stack trace of your code from the point where the connection was first added to the ConnectionMonitor.

By default, entries are written to the log every hour for any connections that have been opened for more than 180 seconds. You can modify this behavior by changing the constants defined at the top of the ConnectionUtility class:

public class ConnectionUtility
{
  // change this name to use a different event source
  public const string EventLogSource = "ConnectionMonitoring";

  // Repeat seconds is how often the event log is written to:
  // the default is 1 hour.
  public const int RepeatSeconds = 3600;

  // this is how many seconds the connection must be open
  // before it is written to the EventLog.
  // the default is 3 minutes
  public const int OpenSeconds = 180;

  // change this to false to disable automatic logging
  public const bool UseLogging = true;
}

How the ConnectionMonitor works

If you've ever examined a System.Exception in a debugger you might have noticed that it includes a very convenient StackTrace property. This string contains the current stack of methods that were invoked when the exception was thrown. My first thought was that the .NET Common Language Runtime was using some type of undocumented voodoo to create the trace. But not at all! It simply uses the System.Diagnostics.StackTrace class.

To create a stack trace for each connection added to the ConnectionMonitor class I used the following code:

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();
}

Another worry I had when I first came up with the idea for this code was: "How am I going to know when the connections are opened and closed?". Manually telling the ConnectionMonitor class every time this happened would not only be cumbersome, it would probably be so error-prone as to make this an exercise in futility. Fortunately, the connection classes in .NET provide the StateChange event which signals when the connection is opened or closed. This made it easy to monitor those events.

//..
// 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();
 }
}

Note that the StateChange event was not added until .NET version 1.1. If you're using version 1.0 this isn't going to work for you.

Handling the Application_End event

If your web application is restarted (either by you or IIS), the ConnectionMonitor object stored in the HttpApplicationState will be lost. You can add some code to the Application_End event handler (found in the Global.asax.cs of your web project) to force the monitor to write to the event log before it is gone. You can also close any stale connections at that time if you choose. Unfortunately, you can't use the ConnectionUtility to get the monitor during the Application_End event. ConnectionUtility uses HttpContext.Current to get the HttpApplicationState. But, during Application_End, HttpContext.Current returns null:

protected void Application_End(Object sender, EventArgs e)
{
 ConnectionMonitor monitor = 
   Application[ConnectionMonitor.CacheName] as ConnectionMonitor;

  if ( monitor != null )
  {    
      EventLog logger = monitor.Logger;

      if ( logger != null )
      {
          monitor.ForceLogging();
          logger.WriteEntry("---Application Ending---", 
                                EventLogEntryType.Information);
      }
      
      //Uncomment out the following statement to close connections
      //that have been opened for longer than 180 seconds
      //monitor.Close( 180 );

  }
}

It is not clear to me what happens to open connections when a web application is restarted. I don't know if they are reclaimed by the system or lost for good. If you're concerned, it might be best to uncomment the monitor.Close statement and change the number of seconds from 180 to 0.

Event log permissions

If you're not currently writing to the event log in your web applications, getting the permissions set up can be tricky (particularly if you have to wait for an administrator to provide you with access). The Event Log is stored in the Window's registry under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog. You can use the Registry Editor to view or change the permissions.

If you're unable to get this working correctly, I suggest the following. First disable event logging in the ConnectionUtility class by changing the const UseLogging (show above) from true to false. Then periodically browse the ShowConnectionStatus.aspx page (provided in the sample code) to view the status of open connections.

Thread safety

The HttpApplicationState can be accessed by multiple threads at the same time. Therefore, to prevent invalid data, the ConnectionMonitor had to be designed with this in mind. This mainly involved using lock statements liberally. For example:

// Adds a new connection to the list
public void Add( ConnectionInfo item )
{
    lock ( this )
    {
        mList.Add( item );
    }
}

I'm not really an expert in multi-thread access, however. Although I've not encountered any problems, perhaps some kind reader more knowledgeable in this sort of thing could provide suggestions if they are needed.

Limitations

Because the ConnectionUtility uses the HttpApplicationState class, it is not suitable for use in web farms or web gardens. ConnectionMonitor itself has no such limitations. But you would need to figure out a way to store a ConnectionMonitor object that can be retrieved across different servers. Whether this is possible or not, I don't know.

Summary

The ConnectionMonitor class provides a method to locate leaked connections in ASP.NET web applications. It also optionally enables automatic event logging complete with stack trace so that connection problems can be easily located and diagnosed. As stated earlier, the one potential downside to using this tool is that you must manually add each connection you create to the ConnectionMonitor. But if you're having connection leaks it will be well worth the effort. When I added connection monitoring to my production web sites, I found one leak the first time the Event Log was written. The stack trace pointed me to some code where I had simply forgotten to close the connection! Several other leaks were found over the next few weeks when exceptions were thrown and the offending code wasn't properly written using try/finally blocks.