My PHP/MS Sql Server 2005/win 2003 Application occasionally becomes very unresponsive, the memory/cpu usage does not spike. If i try to open any new connection from sql management studio, then the it just hangs at the open connection dialog box. how to deterime the total number of active connections ms sql server 2005
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)