How to determine total number of open/active connections in 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 LoginNameFROM    sys.sysprocessesWHERE     dbid > 0GROUP BY     dbid, loginame

And this gives the total:

SELECT     COUNT(dbid) as TotalConnectionsFROM    sys.sysprocessesWHERE     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)

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 repositoryusing (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);            }        }    }}

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, hostprocessFROMsys.sysprocesses with (nolock)WHERE dbid > 0GROUP BY dbid, loginame, hostname, hostprocess