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.