0

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

1629

enter image description here

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

Vy Do
  • 46,709
  • 59
  • 215
  • 313
  • 2
    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
  • 1
    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. – Vy Do Oct 25 '20 at 10:54

4 Answers4

4

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;
MintBerryCRUNCH
  • 530
  • 4
  • 21
Kshitij
  • 66
  • 4
  • 1
    It cannot list all and enough thousands of connections as mentioned. https://user-images.githubusercontent.com/1328316/97105313-6656f580-16ec-11eb-8b00-b439487d5606.png – Vy Do Oct 25 '20 at 11:04
  • 1
    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
2

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)
MintBerryCRUNCH
  • 530
  • 4
  • 21
  • 1
    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 – Vy Do Oct 25 '20 at 10:55
  • @DoNhuVy, according to [the documentation](https://learn.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
  • 1
    @DanGuzman what is *DMVs*? Let's post your answer, I will accept it. – Vy Do Oct 25 '20 at 11:28
  • @DoNhuVy, DMVs are [Dynamic Management Views](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-management-views). – Dan Guzman Oct 25 '20 at 11:56
2

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);
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
0

In windows 10, you can just use the command line utility netstat. For general connections, use:

netstat -a

To get the output :

Active Connections

  Proto  Local Address          Foreign Address        State
  TCP    0.0.0.0:80             DESKTOP-VH14G86:0      LISTENING
  TCP    0.0.0.0:135            DESKTOP-VH14G86:0      LISTENING
  TCP    0.0.0.0:443            DESKTOP-VH14G86:0      LISTENING
  TCP    0.0.0.0:445            DESKTOP-VH14G86:0      LISTENING
  TCP    0.0.0.0:902            DESKTOP-VH14G86:0      LISTENING
  TCP    0.0.0.0:912            DESKTOP-VH14G86:0      LISTENING
  TCP    0.0.0.0:5040           DESKTOP-VH14G86:0      LISTENING
  TCP    0.0.0.0:5357           DESKTOP-VH14G86:0      LISTENING
  TCP    0.0.0.0:7680           DESKTOP-VH14G86:0      LISTENING
  TCP    0.0.0.0:49664          DESKTOP-VH14G96:0      LISTENING
  TCP    0.0.0.0:49665          DESKTOP-VH14G86:0      LISTENING
  TCP    0.0.0.0:49666          DESKTOP-V14G86:0      LISTENING
  TCP    0.0.0.0:49667          DESKTOP-VH14G86:0      LISTENING
  TCP    0.0.0.0:49668          DESKTOP-VH14G86:0      LISTENING
  TCP    0.0.0.0:49669          DESKTOP-VH14G86:0      LISTENING
  TCP    0.0.0.0:52145          DESKTOP-VH14G86:0      LISTENING
  TCP    127.0.0.1:1434         DESKTOP-VH14G86:0      LISTENING
  TCP    127.0.0.1:8307         DESKTOP-VH14G86:0      LISTENING
  TCP    127.0.0.1:49905        DESKTOP-VH14G86:0      LISTENING
  TCP    127.0.0.1:58238        DESKTOP-VH14G86:58239  ESTABLISHED
  TCP    127.0.0.1:58239        DESKTOP-VH14G86:58238  ESTABLISHED
  TCP    127.0.0.1:58244        DESKTOP-VH14G86:58245  ESTABLISHED
  TCP    127.0.0.1:58245        DESKTOP-VH14G86:58244  ESTABLISHED
  TCP    172.16.226.163:139     DESKTOP-VH14G86:0      LISTENING

If you know the port in which Sql Server listens, say port 1434, enter netstat -ano|findstr ":1434":

C:\WINDOWS\system32>netstat -ano| findstr ":1434"
  TCP    127.0.0.1:1434         0.0.0.0:0              LISTENING       1860
  TCP    [::1]:1434             [::]:0                 LISTENING       1860
  UDP    0.0.0.0:1434           *:*                                    4072
  UDP    [::]:1434              *:*                                    4072
buddemat
  • 4,552
  • 14
  • 29
  • 49
MSIS
  • 157
  • 1
  • 8