How to find leaking db connection pool handle?

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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
}

Method 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.

Method 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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0