March 24, 2008

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, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

23 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

Fatih Küçükbaltacı said...

Very nice. Thanks.

Kelly said...

Thanks, it is useful

Anonymous said...

very very useful thanks a lot

veeramanink 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!

Anonymous said...

Thanks

Sattish 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?

Leo Cepeda said...

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