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
About The Author
Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of
DotNetCurry,
DNC Magazine for Developers,
SQLServerCurry and
DevCurry. He has also authored a couple of books
51 Recipes using jQuery with ASP.NET Controls and a new one recently at
The Absolutely Awesome jQuery CookBook.
Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.
Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook
22 comments:
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!
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
Excelent! Very useful. Thanks.
thanks so much!
Great post! I get error maximum sqlconnection pool and i had to restart application. how can i fix that?
Is it okay to install SP4 on SQL 2005 with active connections on it?
Post a Comment