How to find the number of concurrent SQL Server connections

Question

I have a SQL Server that is reaching the max limit of concurrent connections. I have many different servers & services connecting to one SQL Server at the same time.

I did find another query that seems to work:

SELECT DB_NAME(dbid) AS DBName,
       COUNT(dbid)   AS NumberOfConnections,
       loginame      AS LoginName,
       nt_domain     AS NT_Domain,
       nt_username   AS NT_UserName,
       hostname      AS HostName
FROM   sys.sysprocesses
WHERE  dbid > 0
GROUP  BY dbid,
          hostname,
          loginame,
          nt_domain,
          nt_username
ORDER  BY NumberOfConnections DESC;

However, this gives me the number of connections which is good. How do i further dig down this to find to see the each connection and what action they are doing?

Answer 1

the sql command "sp_who" which provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine (MSDN) From here, you should be able to send the results into a temp table and group them by servername.

such as the following...

CREATE TABLE #tbl (
      spid      int
    , ecid      int
    , status    varchar(50)
    , loginame  varchar(255)
    , hostname  varchar(255)
    , blk       varchar(50)
    , dbname    varchar(255)
    , cmd       varchar(255)
    , request_id varchar(255)
    )
GO

INSERT INTO #tbl EXEC sp_who

SELECT COUNT(0), hostname FROM #tbl group by hostname

DROP TABLE #tbl
GO