如何确定 msqlserver2005中打开/活动连接的总数

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)

If your PHP app is holding open many SQL Server connections, then, as you may know, you have a problem with your app's database code. It should be releasing/disposing those connections after use and using connection pooling. Have a look here for a decent article on the topic...

http://www.c-sharpcorner.com/UploadFile/dsdaf/ConnPooling07262006093645AM/ConnPooling.aspx

As @jwalkerjr mentioned, you should be disposing of connections in code (if connection pooling is enabled, they are just returned to the connection pool). The prescribed way to do this is using the 'using' statement:

// Execute stored proc to read data from repository
using (SqlConnection conn = new SqlConnection(this.connectionString))
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "LoadFromRepository";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ID", fileID);


conn.Open();
using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
if (rdr.Read())
{
filename = SaveToFileSystem(rdr, folderfilepath);
}
}
}
}

see sp_who it gives you more details than just seeing the number of connections

in your case i would do something like this

 DECLARE @temp TABLE(spid int , ecid int, status varchar(50),
loginname varchar(50),
hostname varchar(50),
blk varchar(50), dbname varchar(50), cmd varchar(50), request_id int)
INSERT INTO @temp


EXEC sp_who


SELECT COUNT(*) FROM @temp WHERE dbname = 'DB NAME'

MS SQL knowledge based - How to know open SQL database connection(s) and occupied on which host.

Using below query you will find list database, Host name and total number of open connection count, based on that you will have idea, which host has occupied SQL connection.

SELECT DB_NAME(dbid) as DBName, hostname ,COUNT(dbid) as NumberOfConnections
FROM sys.sysprocesses with (nolock)
WHERE dbid > 0
and len(hostname) > 0
--and DB_NAME(dbid)='master' /* Open this line to filter Database by Name */
Group by DB_NAME(dbid),hostname
order by DBName

I know this is old, but thought it would be a good idea to update. If an accurate count is needed, then column ECID should probably be filtered as well. A SPID with parallel threads can show up multiple times in sysprocesses and filtering ECID=0 will return the primary thread for each SPID.

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

Use this to get an accurate count for each connection pool (assuming each user/host process uses the same connection string)

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

SELECT
[DATABASE] = DB_NAME(DBID),
OPNEDCONNECTIONS =COUNT(DBID),
[USER] =LOGINAME
FROM SYS.SYSPROCESSES
GROUP BY DBID, LOGINAME
ORDER BY DB_NAME(DBID), LOGINAME