At times, we need to check the number of active connections for each Database on our server. This can be done easily using the following script. The script displays the DatabaseName, the number of connections and the login name :
SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections,
loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame
Works very well. Thanks.
ReplyDeletewThanks very much - worked for me
ReplyDelete-Neil
Works for SQL 2005 SP2 - Just copy and execute in a new query. Thanx
ReplyDeleteVery nice. Thanks.
ReplyDeleteThanks, it is useful
ReplyDeletevery very useful thanks a lot
ReplyDeletethanks very very useful
ReplyDeleteThank You !
ReplyDeletewhat should I write to SQL (8) 2000? I get "Invalid object name 'sys.sysprocesses'."
ReplyDeleteShalom!
ReplyDeleteFor SQL Server 2000, change sys.sysprocesses to sysprocesses
hth
Jack
Great Query! Thanks very much!
ReplyDeleteVery useful. Thanks a lot.
ReplyDeleteNice one - thanks!
ReplyDeleteVery helpful. Thank you!
ReplyDelete-Joe, Portsmouth, NH
Great Script.
ReplyDeleteThanks for your work.
Good Job.
ReplyDeleteShould I have any special permissions or role to perform this query? I'm always seing only one active connection, I gues it's the current connection...
ReplyDeleteYes you must have VIEW SERVER STATE permission on the server to see all executing sessions in the instance of SQL Server, otherwise you will see only the current session
ReplyDeleteOn a side note, if you are using SQL 2005 and higher, use equivalent DMV's. Search google or SQL documentation for
sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_exec_requests
Excelent! Very useful. Thanks.
ReplyDeletethanks so much!
ReplyDeleteGreat post! I get error maximum sqlconnection pool and i had to restart application. how can i fix that?
ReplyDeleteIs it okay to install SP4 on SQL 2005 with active connections on it?
ReplyDelete