如何衡量打开数据库连接的数量

I am trying to determine if I have a database connection leak. So I need to see the number of open connections. I have some simple test code that creates a leak:

我试图确定我是否有数据库连接泄漏。所以我需要查看打开连接的数量。我有一些简单的测试代码会产生泄漏：

protected void Page_Load(object sender, EventArgs e)
{
  for(int i = 0; i < 100; i++)
  {
    SqlConnection sql = new SqlConnection(@"Data Source=.\SQLExpress;UID=sa;PWD=fjg^%kls;Initial Catalog=ABC");
    sql.Open();
  }

}

Note there is no .Close and this does infact crash after being run 3 times in quick succession.

请注意，没有.Close，这在快速连续运行3次后确实会崩溃。

In order to measure the leak I am running the Performance monitor and measuring SQLServer: General Statistics/User Connections:

为了测量泄漏，我正在运行性能监视器并测量SQLServer：常规统计/用户连接：

alt text http://www.yart.com.au/*/counter.png

替代文字http://www.yart.com.au/*/counter.png

However, these seem to be zero when I run my code:

但是，当我运行我的代码时，这些似乎为零：

alt text http://www.yart.com.au/*/counter1.jpg

替代文字http://www.yart.com.au/*/counter1.jpg

What should I change to actually see the connections?

我应该改变什么来实际看到连接？

ANSWER

回答

I have approved an answer below. Even though it doesn't use the performance tools, its good enough for my use. Bottom line is I wanted to see how many connections remain open after opening a web page and this did the trick.

我已经批准了以下答案。即使它不使用性能工具，它对我的使用也足够好。最重要的是，我希望看到打开网页后有多少连接保持打开状态，这就行了。

2 个解决方案

#1

You can try running a query against the master db like this:

您可以尝试对主数据库运行查询，如下所示：

SELECT SPID,
       STATUS,
       PROGRAM_NAME,
       LOGINAME=RTRIM(LOGINAME),
       HOSTNAME,
       CMD
FROM  MASTER.DBO.SYSPROCESSES
WHERE DB_NAME(DBID) = 'TEST' AND DBID != 0

See this link for more details.

有关详细信息，请参阅此链接。

#2

Have you tried running the sp_who stored proc? If there are stale open connections they should show up there.

您是否尝试过运行sp_who存储过程？如果有陈旧的开放连接，他们应该出现在那里。

To show just the sa users processes run:

要显示sa用户进程运行：

EXEC sp_who 'sa'

#1