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

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

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

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)