|
|
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
Did you like this post?
|
|
||
|
|
|
|
|
subscribe via rss |
|
subscribe via e-mail |
|
print this post |
|
follow me on twitter |
About The Author
comments
19 Responses to "How to see active connections for each Database in SQL Server 2005"Works very well. Thanks.
wThanks very much - worked for me
-Neil
Works for SQL 2005 SP2 - Just copy and execute in a new query. Thanx
Very nice. Thanks.
Thanks, it is useful
very very useful thanks a lot
thanks very very useful
Thank You !
what should I write to SQL (8) 2000? I get "Invalid object name 'sys.sysprocesses'."
Shalom!
For SQL Server 2000, change sys.sysprocesses to sysprocesses
hth
Jack
Great Query! Thanks very much!
Thanks
Very useful. Thanks a lot.
Nice one - thanks!
Very helpful. Thank you!
-Joe, Portsmouth, NH
Great Script.
Thanks for your work.
Good Job.
Should 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...
Yes 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
On 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
Post a Comment