CODEWITHSUNDEEP
sqlsql-serverconnection
user1158745
user1158745

Reputation: 2490

How to find the number of concurrent SQL Server connections

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?

Upvotes: 7

Views: 42725

Answers (2)

the_marcelo_r
the_marcelo_r

Reputation: 1856

Are you talking about how to check the connections that are reaching your SQL Server instance? How about some DOS commands? try to search for the port 1433 or for the PID of your SQL Server process.

netstat -nao | find "1433"

To find the PID (process identifier) that you are looking for, just go to your task manager and customize the processes view, mode details here: http://www.mydigitallife.info/how-to-get-and-view-process-identifier-process-id-or-pid-on-windows/

Upvotes: 3

Xabur
Xabur

Reputation: 49

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

Upvotes: 4

Related Questions