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

Question

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

Answer 1

This shows the no 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'

Answer 2

If you're 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

Answer 3

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