如何找到泄漏数据库连接池句柄？

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.

我想要做的是：下次我们收到此错误时，让系统转储一个列表，其中包含哪些proc或http请求包含所有句柄，因此我可以找出导致问题的代码。

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?

有没有办法做到这一点？

3 个解决方案

#1

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

监控连接池有一些很好的链接。谷歌搜索“.net连接池监控”。

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.

我前面提到过的一篇文章是Bill Vaughn的文章（请注意，这篇文章虽旧，但仍包含有用的信息）。它有关于监控连接池的一些信息，但也有一些关于泄漏可能发生的深刻见解。

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.

·将SQL事件探查器与SQLProfiler TSQL_Replay模板一起用于跟踪。对于那些熟悉Profiler的人来说，这比使用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.

·运行SP_WHO或SP_WHO2，它在sysprocesses表中返回显示每个进程当前状态的所有工作进程的信息。通常，每个连接都有一个SPID服务器进程。如果您使用连接字符串中的Application Name参数命名连接，则很容易找到。

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

·使用性能监视器（PerfMon）监视池和连接。我接下来详细讨论这个问题。

· 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

与往常一样，请查看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.

一旦确认池没有回收连接，您可以尝试的另一件事是利用StateChange事件来确认何时打开和关闭连接。如果您发现打开的状态更改比关闭更多，那么这表明某处存在泄漏。然后，您还可以在statechanged事件中记录数据以及时间戳，如果您在应用程序上有任何其他日志记录，则可以开始解析日志文件，以查看状态更改为关闭以打开的情况，没有相应的开放关闭。有关如何处理StateChangedEvent的详细信息，请参阅此链接。

#2

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

如果你足够幸运，连接创建/打开是集中的，那么下面的类应该可以很容易地发现泄漏的连接。请享用：）

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

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

为了找到长时间运行的存储过程，我可以回过头来找到调用SP的方法。

dont know if that'll help

不知道这是否有帮助

#1