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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

23 Responses to "How to see active connections for each Database in SQL Server 2005"
  1. Anonymous said...
    May 26, 2008 at 9:49 AM

    Works very well. Thanks.

  2. Anonymous said...
    June 18, 2008 at 7:02 AM

    wThanks very much - worked for me
    -Neil

  3. Anonymous said...
    October 6, 2008 at 10:25 PM

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

  4. Fatih Küçükbaltacı said...
    January 10, 2009 at 12:05 AM

    Very nice. Thanks.

  5. Kelly said...
    January 16, 2009 at 12:53 AM

    Thanks, it is useful

  6. Anonymous said...
    March 6, 2009 at 2:50 AM

    very very useful thanks a lot

  7. veeramanink said...
    March 6, 2009 at 2:50 AM

    thanks very very useful

  8. Anonymous said...
    January 15, 2010 at 12:33 AM

    Thank You !

  9. שעון נוכחות said...
    February 8, 2010 at 4:19 AM

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

  10. Anonymous said...
    February 24, 2010 at 4:07 AM

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

  11. Dave said...
    June 15, 2010 at 5:26 AM

    Great Query! Thanks very much!

  12. Anonymous said...
    August 12, 2010 at 2:40 AM

    Thanks

  13. Sattish said...
    November 16, 2010 at 9:03 AM

    Very useful. Thanks a lot.

  14. Anonymous said...
    January 13, 2011 at 3:52 AM

    Nice one - thanks!

  15. Anonymous said...
    January 27, 2011 at 11:31 AM

    Very helpful. Thank you!

    -Joe, Portsmouth, NH

  16. Anonymous said...
    July 18, 2011 at 1:35 AM

    Great Script.
    Thanks for your work.

  17. Anonymous said...
    August 8, 2011 at 8:13 AM

    Good Job.

  18. Anonymous said...
    August 23, 2011 at 6:55 AM

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

  19. Suprotim Agarwal said...
    August 23, 2011 at 7:18 AM

    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

  20. Daniel Martín said...
    February 25, 2013 at 6:40 AM

    Excelent! Very useful. Thanks.

  21. thiet ke web gia re said...
    March 14, 2013 at 6:47 AM

    thanks so much!

  22. sửa điều hòa, tủ lạnh tại nhà ở Hà nội said...
    June 19, 2013 at 8:23 AM

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

  23. Leo Cepeda said...
    January 8, 2014 at 8:21 AM

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

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions