MS SQL Query to list all active connections to all DBs on a given SQl instance

Query: To List all active connections to all DBs on given SQL instance

SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections, loginame as LoginName FROM sys.sysprocesses
WHERE dbid > 0GROUP BY dbid, loginame

Results:

DatabaseName NoOfConnections LoginName
Db01 2 NT AUTHORITYSYSTEM
Db02 1 NT AUTHORITYSYSTEM
master 16 sa
QADB01 1 Testadmin-user

Query: To List all active connections to specific DB on given SQL instance

SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections, loginame as LoginName FROM sys.sysprocesses WHERE (dbid > 0 AND db_name(dbid) like '%db0%')
GROUP BY dbid, loginame

Results:

DatabaseName NoOfConnections LoginName
Db01 2 NT AUTHORITYSYSTEM
Db02 1 NT AUTHORITYSYSTEM
     
     

Leave a Reply

Your email address will not be published. Required fields are marked *