How to list all connections in SQL Server?

Question

I am using this version of SQL Server

Microsoft SQL Server 2019 (RTM-GDR) (KB4517790) - 15.0.2070.41 (X64)   Oct 28 2019 19:56:59   Copyright (C) 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19041: ) (Hypervisor)

I run query

select @@CONNECTIONS;

result

I want list all of connections (in this case, 1629) to see what under the hood, how to do this?

Does this answer your question? [How do I see active SQL Server connections?](https://stackoverflow.com/questions/1248423/how-do-i-see-active-sql-server-connections) — Frédéric, Oct 25 '20 at 10:52
I tried this solution https://stackoverflow.com/a/9392845/3728901 but it return 2 rows, it is not what I expected. I also tried other solutions in other answers. I also know how to using Google search. — James Graham, Oct 25 '20 at 10:54

score 2 · Answer 1 · answered Oct 25 '20 at 11:54

The @@CONNECTIONS documentation states:

Returns the number of attempted connections - both successful and unsuccessful - since SQL Server was last started.

Although SQL Server DMVs will show current connections, these will not include detail of past connections nor failed connection attempts. In order to get historical detail of successful and failed logins you could use SQL Server Audit or create an Extended Event trace with a file target. Below is example DDL for the trace method:

CREATE EVENT SESSION [logins] ON SERVER 
ADD EVENT sqlserver.process_login_finish
ADD TARGET package0.event_file(SET filename=N'logins')
WITH (STARTUP_STATE=ON);

score 0 · Answer 2 · answered Oct 25 '20 at 10:54

0

You could try the below:

SELECT DB_NAME(dbid) AS DBName,
COUNT(dbid) AS NumberOfConnections,
loginame
FROM    sys.sysprocesses
GROUP BY dbid, loginame
ORDER BY DB_NAME(dbid)

answered Oct 25 '20 at 10:54

Foster90

129
1
11

It cannot list thousand of connections as I mentioned. I tried it in my computer. Let's see something https://user-images.githubusercontent.com/1328316/97105178-9f429a80-16eb-11eb-8eb9-0914b82be389.png – James Graham Oct 25 '20 at 10:55
@DoNhuVy, according to [the documentation](https://docs.microsoft.com/en-us/sql/t-sql/functions/connections-transact-sql), `@@CONNECTIONS` returns the number of attempted connections - both successful and unsuccessful - since SQL Server was last started. The historical detail is not available via DMVs. You need to create a trace if you need that info. – Dan Guzman Oct 25 '20 at 11:05
@Foster90, `sys.sysprocesses` is a view deprecated with the SQL 2005 release and should be avoided in moder SQL Server versions. – Dan Guzman Oct 25 '20 at 11:07
@DanGuzman what is *DMVs*? Let's post your answer, I will accept it. – James Graham Oct 25 '20 at 11:28
@DoNhuVy, DMVs are [Dynamic Management Views](https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-management-views). – Dan Guzman Oct 25 '20 at 11:56

score 0 · Answer 3 · edited Oct 25 '20 at 11:00

0

You can use the below query to find out the active connections -

SELECT
  conn.session_id,
  host_name,
  program_name,
  nt_domain,
  login_name,
  connect_time,
  last_request_end_time
FROM sys.dm_exec_sessions AS sess
INNER JOIN sys.dm_exec_connections AS conn ON sess.session_id = conn.session_id;

edited Oct 25 '20 at 11:00

Foster90

129
1
11

answered Oct 25 '20 at 10:58

Kshitij

1
3

It cannot list all and enough thousands of connections as mentioned. https://user-images.githubusercontent.com/1328316/97105313-6656f580-16ec-11eb-8b00-b439487d5606.png – James Graham Oct 25 '20 at 11:04
This function returns the number of attempted connections - both successful and unsuccessful - since SQL Server was last started. SQL Server did not store all information about these connections... and this query returns the no of active connection :) – Kshitij Oct 25 '20 at 11:33

How to list all connections in SQL Server?

3 Answers3