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 SqlConnection
s, 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();
cnn.Close();
The fix of course is as follows:
SqlConnection cnn = new SqlConnection(myConnectionString);
try
{
cnn.Open();
}
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
{
public const string EventLogSource = "ConnectionMonitoring";
public const int RepeatSeconds = 3600;
public const int OpenSeconds = 180;
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.
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);
}
}
}
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:
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.