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.
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
DbConnection, the base class from which all connection classes are derived.)
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);
The fix of course is as follows:
SqlConnection cnn = new SqlConnection(myConnectionString);
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
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();
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
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
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
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
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 );
frame.GetMethod().DeclaringType, frame.GetMethod().Name );
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.
new StateChangeEventHandler( Connection_StateChange );
void Connection_StateChange(object sender,
if ( ( e.CurrentState & ConnectionState.Open ) != 0 )
if ( e.CurrentState == ConnectionState.Closed )
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
HttpContext.Current to get the
HttpApplicationState. But, during
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 )
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
false. Then periodically browse the
ShowConnectionStatus.aspx page (provided in the sample code) to view the status of open connections.
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.
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.
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