find number of open connection on database

Question

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.

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.

Thanks in advance.

Answer 1

This shows the number of connections per each 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/

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

Answer 2

Sql query to get the current active connection

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

Answer 3

You might want to read up on connection pooling: 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.

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.

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:

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.