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

Sirois

Answer 1

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)

Goda

Wonderful thank you very much. For novices like me, start SQL Server Management Studio, right click on your database, select New Query, paste this in and click the "! Go" button. – Mission

This should be neither accepted, nor highest voted answer as it is simply incorrect. You can only rely on the returned number if you are logged in as sa. If you are logged in as a non-sa user, you will see 1 and that will not be representative of the actual connections. – Judkins

@ajeh: It's implicit that you have sufficient permissions to carry out the task. Your comment is redundant. – Goda

@MitchWheat It's recommended to comment in your answer that to get actual/all connections of the SQL Server, the account used to run this script requires 'sysadmin' permission. – Exhalation

@ IEBasara: It's implicit. Why would you expect a non-admin to be able to view such information? – Goda

Late to the party .. but .. the sysadmin role required (edit comment) saved my buttox. I kept getting 1 assuming I did have the right perms. Phew! fixed and sovled. win :money_with_wings: – Equable

Answer 2

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

Animism

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

Goda

The asker mentioned they were using PHP, so the code sample may not be appropriate for them. The garbage collector should automatically cleans up non-persistent SQL Server connections when there are no more references to them (and all references would be dropped at the end of the page cycle), but maybe the asker is using persistent connections, which requires intelligent connection reuse. – Concatenate

Answer 4

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

Fritillary

Answer 5

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

Chuckhole

Answer 6

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'

Liggitt

Answer 7

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

Joacima

Answer 8

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

Certify

Answer 9

Glenn Barry's connections by IP (Query 39) are pretty helpful as well.

SELECT ec.client_net_address, es.[program_name], es.[host_name], es.login_name, 
COUNT(ec.session_id) AS [connection count] 
FROM sys.dm_exec_sessions AS es WITH (NOLOCK) 
INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK) 
ON es.session_id = ec.session_id 
GROUP BY ec.client_net_address, es.[program_name], es.[host_name], es.login_name  
ORDER BY ec.client_net_address, es.[program_name] OPTION (RECOMPILE);

Fruitage