How to find leaking db connection pool handle?

Question

I'm seeing the dreaded "The timeout period elapsed prior to obtaining a connection from the pool" error.

I've searched the code for any unclosed db connections, but couldn't find any.

What I want to do is this: the next time we get this error, have the system dump a list of which procs or http requests are holding all the handles, so I can figure out which code is causing the problem.

Even better would be to see how long those handles had been held, so I could spot used-but-unclosed connections.

Is there any way to do this?

Answer 1

If you are lucky enough that connection creation/opening is centralized then the following class should make it easy to spot leaked connections. Enjoy :)

using System.Threading; // not to be confused with System.Timer
/// <summary>
/// This class can help identify db connection leaks (connections that are not closed after use).
/// Usage:
/// connection = new SqlConnection(..);
/// connection.Open()
/// #if DEBUG
/// new ConnectionLeakWatcher(connection);
/// #endif
/// That's it. Don't store a reference to the watcher. It will make itself available for garbage collection
/// once it has fulfilled its purpose. Watch the visual studio debug output for details on potentially leaked connections.
/// Note that a connection could possibly just be taking its time and may eventually be closed properly despite being flagged by this class.
/// So take the output with a pinch of salt.
/// </summary>
public class ConnectionLeakWatcher : IDisposable
{
    private readonly Timer _timer = null;

    //Store reference to connection so we can unsubscribe from state change events
    private SqlConnection _connection = null;

    private static int _idCounter = 0;
    private readonly int _connectionId = ++_idCounter;

    public ConnectionLeakWatcher(SqlConnection connection)
    {
        _connection = connection;
        StackTrace = Environment.StackTrace;

        connection.StateChange += ConnectionOnStateChange;
        System.Diagnostics.Debug.WriteLine("Connection opened " + _connectionId);

        _timer = new Timer(x =>
        {
            //The timeout expired without the connection being closed. Write to debug output the stack trace of the connection creation to assist in pinpointing the problem
            System.Diagnostics.Debug.WriteLine("Suspected connection leak with origin: {0}{1}{0}Connection id: {2}", Environment.NewLine, StackTrace, _connectionId);
            //That's it - we're done. Clean up by calling Dispose.
            Dispose();
        }, null, 10000, Timeout.Infinite);
    }

    private void ConnectionOnStateChange(object sender, StateChangeEventArgs stateChangeEventArgs)
    {
        //Connection state changed. Was it closed?
        if (stateChangeEventArgs.CurrentState == ConnectionState.Closed)
        {
            //The connection was closed within the timeout
            System.Diagnostics.Debug.WriteLine("Connection closed " + _connectionId);
            //That's it - we're done. Clean up by calling Dispose.
            Dispose();
        }
    }

    public string StackTrace { get; set; }

    #region Dispose
    private bool _isDisposed = false;

    public void Dispose()
    {
        if (_isDisposed) return;

        _timer.Dispose();

        if (_connection != null)
        {
            _connection.StateChange -= ConnectionOnStateChange;
            _connection = null;
        }

        _isDisposed = true;
    }

    ~ConnectionLeakWatcher()
    {
        Dispose();
    }
    #endregion
}

Answer 2

There are some good links for monitoring connection pools. Do a google search for ".net connection pool monitoring".

One article I referred to a while back was Bill Vaughn's article (Note this is old but still contains useful info). It has some info on monitoring connection pools, but some great insights as to where leaks could be occuring as well.

For monitoring, he suggests;

"Monitoring the connection pool

Okay, so you opened a connection and closed it and want to know if the connection is still in place—languishing in the connection pool on an air mattress. Well, there are several ways to determine how many connections are still in place (still connected) and even what they are doing. I discuss several of these here and in my book:

· Use the SQL Profiler with the SQLProfiler TSQL_Replay template for the trace. For those of you familiar with the Profiler, this is easier than polling using SP_WHO.

· Run SP_WHO or SP_WHO2, which return information from the sysprocesses table on all working processes showing the current status of each process. Generally, there’s one SPID server process per connection. If you named your connection, using the Application Name argument in the connection string, it’ll be easy to find.

· Use the Performance Monitor (PerfMon) to monitor the pools and connections. I discuss this in detail next.

· Monitor performance counters in code. This option permits you to display or simply monitor the health of your connection pool and the number of established connections. I discuss this in a subsequent section in this paper."

Edit:

As always, check out some of the other similar posts here on SO

Second Edit:

Once you've confirmed that connections aren't being reclaimed by the pool, another thing you could try is to utilise the StateChange event to confirm when connections are being opened and closed. If you are finding that there are a lot more state changes to opened than to closed, then that would indicate that there are leaks somewhere. You could also then log the data in the statechanged event along with a timestamp, and if you have any other logging on your application, you could start to parse the log files for instances where there appears to be state changes of closed to open, with no corresponding open to closed. See this link for more info on how to handle the StateChangedEvent.

Answer 3

i've used this

http://www.simple-talk.com/sql/performance/how-to-identify-slow-running-queries-with-sql-profiler/

to find long running stored procedures before, i can then work back and find the method that called the SP.

dont know if that'll help