How to see active connections for each Database in SQL Server 2005

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:

Anonymous said...

Works very well. Thanks.

Anonymous said...

wThanks very much - worked for me
-Neil

Anonymous said...

Works for SQL 2005 SP2 - Just copy and execute in a new query. Thanx

Anonymous said...

Very nice. Thanks.

Anonymous said...

Thanks, it is useful

Anonymous said...

very very useful thanks a lot

veeramani said...

thanks very very useful

Anonymous said...

Thank You !

שעון נוכחות said...

what should I write to SQL (8) 2000? I get "Invalid object name 'sys.sysprocesses'."

Anonymous said...

Shalom!
For SQL Server 2000, change sys.sysprocesses to sysprocesses
hth
Jack

Dave said...

Great Query! Thanks very much!

Unknown said...

Very useful. Thanks a lot.

Anonymous said...

Nice one - thanks!

Anonymous said...

Very helpful. Thank you!

-Joe, Portsmouth, NH

Anonymous said...

Great Script.
Thanks for your work.

Anonymous said...

Good Job.

Anonymous said...

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...

Suprotim Agarwal said...

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

Daniel Martín said...

Excelent! Very useful. Thanks.

thiet ke web gia re said...

thanks so much!

sửa điều hòa, tủ lạnh tại nhà ở Hà nội said...

Great post! I get error maximum sqlconnection pool and i had to restart application. how can i fix that?

Unknown said...

Is it okay to install SP4 on SQL 2005 with active connections on it?