跟踪连接泄漏 [英] Track connection leaks

问题描述

我们有一个似乎有连接泄漏的应用程序（SQL Server表示已达到最大池大小）。我独自在我的dev机器（显然），只是通过导航的应用程序，我触发这个错误。 SQL Server活动监视器显示使用我的数据库的大量进程。

我想查找哪些文件打开连接，但不使用它。我正在考虑使用类似grep的东西，对于每个文件，计数.Open（）的数量和.Close（）的数量，并获得数字不相等的文件。是现实吗？

奖金问题：在SQL Server活动监视器中找到的进程对应的连接？如果没有，我怎么知道我的数据库打开了多少连接？

应用程序在asp.net（vb）3.5，与SQL Server 2005。目前不使用linq（尚未）或类似的东西。

感谢

解决方案>

当从SQL Server端查看代码时，您可以运行以下查询以获取上次在睡眠连接上运行的查询的视图。

  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 
交叉应用sys.dm_exec_sql_text（ec.most_recent_sql_handle）AS dest 
其中es.status ='sleeping'
  

从应用程序端可以使用sos.dll进行调试在以下文章中：

• 如何解决泄漏的SqlConnection对象第1部分


• 如何解决泄漏的SqlConnection对象第2部分

你需要更多关于如何使用windbg的信息，这些文章是一个很好的介绍：

• WinDBG入门第1部分


• WinDBG入门第2部分

We have an app which seems to have connection leaks (SQL Server says that the max pool size have 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.

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 ?

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 ?

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

Thanks

解决方案

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'

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

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

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

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