Find Number of Connections on Databases and Instance Used By Logins
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.
CLICK HERE to watch live practical.
Reference: Manzoor Siddiqui [www.SQLServerLog.com]