Find Number of Connections on Databases and Instance Used By Logins

by Manzoor Siddiqui · Published October 7, 2015 · Updated October 29, 2015

We have a query which is very useful for DBAs and database users to find total number of connections used by particular login on database instance for all databases or on particular database. Here I am using object sys.sysprocesses which will provide us this detail.

To Find All Connections on Database Instance, you can use below script.

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

Here is the output for the query from my test server.

Also, you can find the total connections for specific database by using below query. Here I am looking for master database.

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

Output of above query is given as below. Here I have mentioned master database, you can replace this database name with your actual database name and also to sort the total number of connections, you can use ORDER BY NumberOfConnections DESC.