Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Number of sessions per database

Sometimes when we need to do some maintenance on a SQL server, you need to verify which users are connected or which databases are being used. A quick and easy wayto check the server utilization is simply to identify which databases are being accessedat that time. Thus, I present two ways of doing this verification:

 

1) Using sysprocesses (deprecated)

SELECT DB_NAME(p.dbid) db, COUNT(*) quantity
FROM master.dbo.sysprocesses p
WHERE p.spid > 50
group by DB_NAME(p.dbid)
ORDER BY 1

 

2) Using DMV

SELECT db_name(l.resource_database_id) db, COUNT(*) quantity
FROM sys.dm_tran_locks l
GROUP BY db_name(l.resource_database_id)
ORDER BY 1

Comments

Posted by Dukagjin Maloku on 16 December 2010

Cool info, thanks for sharing!

Leave a Comment

Please register or log in to leave a comment.