如何跟踪数据库连接泄漏 [英] How to track database connection leaks

问题描述

我们有一个似乎连接泄漏的应用程序(SQL Server说已达到最大池大小).我独自一人在开发机器上(很明显)，仅通过浏览该应用程序，我便触发此错误. "SQL Server活动"监视器显示使用我的数据库的大量进程.

We have an app which seems to have connection leaks (SQL Server says that the max pool size has been reached). I am alone on my dev machine (obviously), and just by navigating the app, I trigger this error. The SQL Server Activity monitor shows a great number of processes using my database.

我想查找哪些文件打开了连接但不使用它.我正在考虑使用grep之类的方法，对于每个文件，计算".Open()"的数量和".Close()"的数量，然后获取数字不相等的文件. 现实吗?

I want to find which files open connections but do not use it. I was thinking of using something like grep to, for each file, count the number of ".Open()" and the number of ".Close()", and get the file for which the numbers are not equal. Is it realistic?

奖金问题:SQL Server活动监视器中找到的进程是否与连接相对应?如果没有，我如何找出数据库上打开了多少个连接?

Bonus question: do the processes found in SQL Server Activity Monitor correspond to the connections? If not, how do I find out how many connections are open on my database?

该应用程序位于SQL Server 2005的asp.net(vb)3.5中.我们目前尚未使用LINQ(尚未)或类似的东西.

The app is in asp.net (vb) 3.5, with SQL Server 2005. We currently do not use LINQ (yet) or anything like that.

谢谢

推荐答案

从SQL Server端查看代码时，您可以运行以下查询以查看上次在睡眠连接上运行的查询的视图. (打开的连接什么都不做)

When looking at the code from the SQL Server side you can run the following query to get a view on which queries are last run on sleeping connections. (open connections which are doing nothing)

SELECT ec.session_id, last_read, last_write, text, client_net_address, program_name, host_process_id, login_name
FROM sys.dm_exec_connections  ec
JOIN sys.dm_exec_sessions es
  ON ec.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS dest
where es.status = 'sleeping'

从应用程序方面，您可以按照以下文章中的描述使用sos.dll进行调试:

From the application side you can debug with sos.dll as described in the following articles:

• How to troubleshoot leaked SqlConnection Objects Part 1
• How to troubleshoot leaked SqlConnection Objects Part 2

如果您需要有关如何使用windbg的更多信息，这些文章是不错的介绍:

If you need more information on how to use windbg, these articles are a good intro:

• WinDBG第2部分入门

• Getting Started with WinDBG Part 1
• Getting Started with WinDBG Part 2

这篇关于如何跟踪数据库连接泄漏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！