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
}