Get number of connected users in SQL Server 2000

Question

Hello,

While I'm trying to detach a database through Enterprise Manager, it shows the no. of users accessing the database and it didn't allow us to detach without clearing the database connection.

Well, I want to know whether the result (no. of users connecting a particular database) could be obtained through a SQL query? If yes, how ?

Happiness Always BKR Sivaprakash

Answer 1

This will give you proper results. Add your database name in the query -

select spid, status, loginame, 
hostname, blocked, db_name(dbid) as databasename, cmd 
from master..sysprocesses
where db_name(dbid) like '%<database_name>%'
and spid > 50

This will include logins from SQL Agent. Note that the same user can be using multiple connections from the same application, and thus be counted more than once.

Answer 2

EXEC SP_WHO

or

EXEC SP_WHO2

maybe (think this might be SQL Server 2005 upwards):

SELECT COUNT(*) AS ConnectionCount,
CASE WHEN is_user_process =1 THEN 'UserProcess' ELSE 'System Process' END
FROM sys.dm_exec_sessions
GROUP BY is_user_process