查找数据库上的打开连接数

My web application is in asp.net 2.0,c#2.0 and sql server 208 how can i find number of open connections on my sql server 2008 database.and is there any way to clear connection pool.because my site is hosted on shared hosting and they have provided limited connections. In my codding i have closed all the connection after use, but still i am getting warning for suspending database.

我的web应用程序是在asp.net 2.0，c＃2.0和sql server 208中如何找到我的sql server 2008数据库上的打开连接数。有没有办法清除连接池。因为我的网站托管在共享主机上他们提供的连接有限。在我的编码中，我在使用后关闭了所有连接，但我仍然收到暂停数据库的警告。

Can any one tell me how to find number open connections on database and and how to clear connection pool.

任何人都可以告诉我如何在数据库上找到数字打开连接以及如何清除连接池。

i used using statements for connections and closed all connections after used in finally block. so though there is error it closes oped connections.

我使用using语句进行连接，并在finally块中使用后关闭所有连接。所以尽管有错误，它会关闭oped连接。

Thanks in advance.

提前致谢。

3 个解决方案

#1

This shows the number of connections per each DB:

这显示了每个DB的连接数：

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NoOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame

And this gives total connections:

这给出了总连接：

SELECT 
    COUNT(dbid) as TotalConnections
FROM
    sys.sysprocesses
WHERE 
    dbid > 0

From c#, you can follow :
http://www.c-sharpcorner.com/UploadFile/dsdaf/ConnPooling07262006093645AM/ConnPooling.aspx Another good reference can be found at :
http://www.wduffy.co.uk/blog/monitoring-database-connections/

从c＃开始，您可以访问：http：//www.c-sharpcorner.com/UploadFile/dsdaf/ConnPooling07262006093645AM/ConnPooling.aspx另一个很好的参考资料可以在以下网址找到：http：//www.wduffy.co.uk/blog/监视数据库连接/

Call the static method ReleaseObjectPool on the the OleDbConnection - see http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection.releaseobjectpool.aspx

在OleDbConnection上调用静态方法ReleaseObjectPool - 请参阅http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection.releaseobjectpool.aspx

#2

Sql query to get the current active connection

Sql查询获取当前活动连接

SELECT DB_NAME(dbid) as 'DbNAme', COUNT(dbid) as 'Connections' from master.dbo.sysprocesses with (nolock) WHERE dbid > 0 GROUP BY dbid

you can define dbid if you want connection specific to database

如果需要特定于数据库的连接，则可以定义dbid

#3

You might want to read up on connection pooling: http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

您可能想要了解连接池：http：//msdn.microsoft.com/en-us/library/8xx3tyca.aspx

A separate connection pool is created for each distinct connect string. Further, if you are connecting via integraged security and your web site is using Basic or Windows authentication (rather than anonymous), a separate connection pool will be created for each user of the web site.

为每个不同的连接字符串创建单独的连接池。此外，如果您通过集成安全性进行连接，并且您的网站使用的是基本身份验证或Windows身份验证（而不是匿名身份验证），则将为该网站的每个用户创建一个单独的连接池。

To clear connection pools, the SqlConnection object provides the methods ClearPool() and ClearAllPool()`. However, an individual connection won't be closed and removed from the pool until it is closed or disposed.

要清除连接池，SqlConnection对象提供ClearPool（）和ClearAllPool（）方法。但是，在关闭或处置之前，不会关闭单个连接并将其从池中移除。

All the various objects involved in the execution of the sql query that implement IDisposable should be wrapped in a using statement to guaranteed proper disposal. Something along these lines:

执行IDisposable的sql查询所涉及的所有各种对象都应该包含在using语句中，以保证正确处理。这些方面的东西：

IEnumerable<BusinessObject> list = new List<BusinessObject>() ;

using ( SqlConnection  connection = new SqlConnection( credentials ) )
using ( SqlCommand     command    = connection.CreateCommand() )
using ( SqlDataAdapter adapter    = new SqlDataAdapter( command ) )
using ( DataSet        results    = new DataSet() )
{

  command.CommandType = CommandType.StoredProcedure ;
  command.CommandText = @"someStoredProcedure" ;

  try
  {
    connection.Open() ;
    adapter.Fill( results ) ;
    connection.Close() ;

    list = TransformResults( results ) ;

  }
  catch
  {
    command.Cancel() ;
    throw
  }

}

return list ;

You can examine what SPIDs are open in Sql Server either by executing the stored procedure sp_who (must have the appropriate admin permissions in the SQL Server). You can also use perfmon.

您可以通过执行存储过程sp_who（必须在SQL Server中具有相应的管理权限）来检查Sql Server中打开的SPID。你也可以使用perfmon。

#1