Find Number of Connections on Databases and Instance Used By Logins

NoOfConnections

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.

NoOfConnectionsInstance

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.

NoOfConnectionsDB

CLICK HERE to watch live practical.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

You may also like...

Leave a Reply

Your email address will not be published.