Monday 14 June 2010

SQL Server 2005: Determining the Total Number of Open/Active Connections

Sometimes applications can become unresponsive or generate SQL Server timeout exceptions (System.Data.SQLClient.SQLException: Timeout Expired) and sometimes you just need to find out how much traffic is hitting your database.

You can find out a few things by using these commands:

Total Number of Connections by Database


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

Total Connections

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

Detailed description of all running processes

sp_who2 'Active'

NB: You probably need to be logged in with SA privileges to run these commands.

0 comments: