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

Rate

Share

Share

Rate