How to determine total number of open/active connections in ms sql server 2005

by

This shows the number of connections per each DB:

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame

And this gives the total:

SELECT 
    COUNT(dbid) as TotalConnections
FROM
    sys.sysprocesses
WHERE 
    dbid > 0

If you need more detail, run:

sp_who2 'Active'

Note: The SQL Server account used needs the ‘sysadmin’ role (otherwise it will just show a single row and a count of 1 as the result)

More Related Contents:

  1. What is the point of “Initial Catalog” in a SQL Server connection string?
  2. Date range overlapping check constraint
  3. Difference between database and schema
  4. Updating image in VB and SQL Server not working [closed]
  5. How do I fix the error ‘Named Pipes Provider, error 40 – Could not open a connection to’ SQL Server’?
  6. Storing a file in a database as opposed to the file system?
  7. How to get Time from DateTime format in SQL?
  8. Querying data by joining two tables in two database on different servers
  9. Natural (human alpha-numeric) sort in Microsoft SQL 2005
  10. Multiple Indexes vs Multi-Column Indexes
  11. Preferred Method of Storing Passwords In Database
  12. How much size “Null” value takes in SQL Server
  13. SQL Server Pivot Table with multiple column aggregates
  14. Using a sort order column in a database table
  15. Why is it considered bad practice to use cursors in SQL Server?
  16. CAST and IsNumeric
  17. SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
  18. Is it possible to force row level locking in SQL Server?
  19. SQL Query to find the last day of the month
  20. SQL Server – Dynamic PIVOT Table – SQL Injection
  21. SQL Server, convert a named instance to default instance?
  22. How to connect to mssql using pdo through PHP and Linux?
  23. Unable to begin a distributed transaction
  24. What happens to the primary key Id when it goes over the limit?
  25. Received an invalid column length from the bcp client for colid 6
  26. How to migrate a PostgreSQL database into a SQLServer one?
  27. How to check which locks are held on a table
  28. How do you kill all current connections to a SQL Server 2005 database?
  29. Why is casting from float to varchar being rounded in SQL Server?
  30. Forgot SQL Server Password

Leave a Comment