How do I see active SQL Server connections?

Question

I am using SQL Server 2008 Enterprise. I want to see any active SQL Server connections, and the related information of all the connections, like from which IP address, connect to which database or something.

Are there existing commands to solve this issue?

check: https://stackoverflow.com/a/23397001/1747983 – Tilo Apr 05 '19 at 17:09 — Tilo, Apr 05 '19 at 17:09
sys.dm_exec_sessions, session id to check >50 – Channa Sep 20 '19 at 20:01 — Channa, Sep 20 '19 at 20:01

score 442 · Answer 1 · edited Nov 10 '17 at 11:29

442

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame
;

See also the Microsoft documentation for sys.sysprocesses.

edited Nov 10 '17 at 11:29

Martin Brown

24,692
14
77
122

answered Feb 22 '12 at 10:16

Syed Umar Ahmed

5,612
1
21
23

7

When automating things, this query might be more useful than sp_who which is more oriented toward displaying. – Colin Jan 14 '13 at 20:09
1

This is my preferred method, but it doesn't fully answer the OP's question. Suggest adding `hostname` to the `SELECT` and `GROUP BY` clauses to see what clients are connected. Also I just realized the Msft typo for `loginame` - is that an artifact from when column names were limited to 8 chars? lol – nothingisnecessary Sep 12 '14 at 23:09
18

[sys.sysprocesses is deprecated](https://msdn.microsoft.com/en-us/library/ms179881.aspx) in more recent versions of SQL Server. It maps to these [three management views](https://msdn.microsoft.com/en-us/library/ms187997.aspx): sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests. – Mike Sherrill 'Cat Recall' Feb 07 '16 at 16:19
1

I like to `ORDER BY 1, 2 DESC, 3` – slartidan Jul 10 '19 at 14:56

score 432 · Accepted Answer · edited Nov 21 '17 at 18:26

432

You can use the sp_who stored procedure.

Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.

edited Nov 21 '17 at 18:26

Peter Mortensen

30,738
21
105
131

answered Aug 08 '09 at 09:27

Mehrdad Afshari

414,610
91
852
789

16

when you have to filter for specific db selecting from sys.sysprocesses is better – Iman Dec 02 '13 at 04:29
2

how would I add a filter for specific databases only? WHERE dbname = 'database name' ?? I tried this and I got an error – NULL.Dude Sep 20 '17 at 16:18
2

@Geo.Dude, Iman Abidi means crafting your own select query from sys.sysprocesses and adding a where clause to that query. You'll have to filter on dbid. You can find the database id in sys.databases (or you can join those two). – bvgheluwe Jan 18 '18 at 08:03

score 66 · Answer 3 · edited Nov 21 '17 at 18:21

66

Apart from sp_who, you can also use the "undocumented" sp_who2 system stored procedure which gives you more detailed information. See Difference between sp_who and sp_who2.

edited Nov 21 '17 at 18:21

Peter Mortensen

30,738
21
105
131

answered Aug 08 '09 at 09:37

Sklivvz

30,601
24
116
172

22

who's connected to dbo.First? – Carl G Jan 12 '13 at 21:52

score 60 · Answer 4 · edited Sep 10 '20 at 01:40

60

Click the "activity monitor" icon in the toolbar.

From Thorsten's comment:

In SQL Server Management Studio, right click on Server, choose "Activity Monitor" from context menu -or- use keyboard shortcut Ctrl + Alt + A.

Reference: Microsoft Docs - Open Activity Monitor in SQL Server Management Studio (SSMS)

edited Sep 10 '20 at 01:40

Pang

9,564
146
81
122

answered Mar 22 '12 at 01:25

Fernando Santos

609
5
2

13

In SQL Server Management Studio, right click on Server, choose "Activity Monitor" from context menu -or- use keyboard shortcut Ctrl+Alt+A – Thorsten Hüglin Feb 20 '15 at 14:48
1

Good option, but it requires more priviledges than DB_NAME(dbid) extraction from sys.sysprocesses. – Der Zinger Jan 26 '16 at 07:18

Marcello Miorelli · Answer 5 · 2022-11-05T07:23:38.930

Below is my script to find all the sessions connected to a database and you can check if those sessions are doing any I/O and there is an option to kill them.

The script shows also the status of each session.

Have a look below.

--==============================================================================
-- See who is connected to the database.
-- Analyse what each spid is doing, reads and writes.
-- If safe you can copy and paste the killcommand - last column.
-- Marcelo Miorelli
-- 18-july-2017 - London (UK)
-- Tested on SQL Server 2016.
--==============================================================================
USE master
go
SELECT
     sdes.session_id
    ,sdes.login_time
    ,sdes.last_request_start_time
    ,sdes.last_request_end_time
    ,sdes.is_user_process
    ,sdes.host_name
    ,sdes.program_name
    ,sdes.login_name
    ,sdes.status

    ,sdec.num_reads
    ,sdec.num_writes
    ,sdec.last_read
    ,sdec.last_write
    ,sdes.reads
    ,sdes.logical_reads
    ,sdes.writes

    ,sdest.DatabaseName
    ,sdest.ObjName
    ,sdes.client_interface_name
    ,sdes.nt_domain
    ,sdes.nt_user_name
    ,sdec.client_net_address
    ,sdec.local_net_address
    ,sdest.Query
    ,KillCommand  = 'Kill '+ CAST(sdes.session_id  AS VARCHAR)
FROM sys.dm_exec_sessions AS sdes

INNER JOIN sys.dm_exec_connections AS sdec
        ON sdec.session_id = sdes.session_id

CROSS APPLY (

    SELECT DB_NAME(dbid) AS DatabaseName
        ,OBJECT_NAME(objectid) AS ObjName
        ,COALESCE((
            SELECT TEXT AS [processing-instruction(definition)]
            FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
            FOR XML PATH('')
                ,TYPE
            ), '') AS Query

    FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)

) sdest
WHERE sdes.session_id <> @@SPID
  AND sdest.DatabaseName ='yourdatabasename'
--ORDER BY sdes.last_request_start_time DESC

--==============================================================================

UPDATE 20th Jan 2022

I have now a better version that uses sys.dm_tran_locks This is particularly useful when you need all users off an specific database, and that's why I have this parameter @dbname

Even if someone just opens SSMS and connect to a database it will show up in this query.

DECLARE @dbname SYSNAME =NULL

SELECT 
        sdes.session_id 
       ,sdes.login_time 
       ,sdes.last_request_start_time
       ,sdes.last_request_end_time
       ,sdes.is_user_process
       ,sdes.host_name
       ,sdes.program_name
       ,sdes.login_name
       ,sdes.status

       ,sdec.num_reads
       ,sdec.num_writes
       ,sdec.last_read
       ,sdec.last_write
       ,sdes.reads
       ,sdes.logical_reads
       ,sdes.writes
       
       ,DatabaseName = COALESCE( db_name(sdes.database_id),  N'')
       ,sdest.ObjName
    ,sdes.client_interface_name
    ,sdes.nt_domain
    ,sdes.nt_user_name
    ,sdec.client_net_address
    ,sdec.local_net_address
    ,sdest.Query
    ,KillCommand  = 'Kill '+ CAST(sdes.session_id  AS VARCHAR)

from sys.dm_tran_locks t
INNER JOIN sys.dm_exec_sessions sdes
        ON t.request_session_id = sdes.session_id

LEFT OUTER JOIN sys.dm_exec_connections AS sdec 
        ON sdec.session_id = sdes.session_id

OUTER APPLY (

                SELECT DB_NAME(dbid) AS DatabaseName
                    ,OBJECT_NAME(objectid) AS ObjName
                    ,COALESCE((
                            SELECT TEXT AS [processing-instruction(definition)]
                            FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle) 
                            FOR XML PATH('')
                                ,TYPE
                            ), '') AS Query

                FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)

    ) sdest
where t.resource_type = 'database' 
  and t.resource_database_id = CASE WHEN @dbname IS NULL 
                                    THEN t.resource_database_id  
                                    ELSE DB_ID(@dbname)  
                               END 
  and t.request_type = 'LOCK' 
  and t.request_status = 'GRANT'

@JacobIRR Yes however, now I use a mix of that with this one: `select request_session_id from sys.dm_tran_locks where resource_type = 'database' and resource_database_id = 3 and request_type = 'LOCK' and request_status = 'GRANT';` — Marcello Miorelli, Jan 20 '22 at 18:40
I will update the answer because It has helped me a lot really — Marcello Miorelli, Jan 20 '22 at 18:41
Note: capital 'T' in line " ON T.request_session_id" should be 't' : 'ON t.request_session_id' (could not edit the answer) — JimiSweden, Nov 05 '22 at 05:32

score 21 · Answer 6 · edited Jun 17 '20 at 01:01

21

MS's query explaining the use of the KILL command is quite useful providing connection's information:

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
JOIN sys.dm_exec_connections AS conn
   ON sess.session_id = conn.session_id;

edited Jun 17 '20 at 01:01

Pang

9,564
146
81
122

answered May 20 '19 at 11:21

Zalakain

597
5
9

score 15 · Answer 7 · answered Aug 22 '12 at 17:43

I threw this together so that you could do some querying on the results

Declare @dbName varchar(150)
set @dbName = '[YOURDATABASENAME]'

--Total machine connections
--SELECT  COUNT(dbid) as TotalConnections FROM sys.sysprocesses WHERE dbid > 0

--Available connections
DECLARE @SPWHO1 TABLE (DBName VARCHAR(1000) NULL, NoOfAvailableConnections VARCHAR(1000) NULL, LoginName VARCHAR(1000) NULL)
INSERT INTO @SPWHO1 
    SELECT db_name(dbid), count(dbid), loginame FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame
SELECT * FROM @SPWHO1 WHERE DBName = @dbName

--Running connections
DECLARE @SPWHO2 TABLE (SPID VARCHAR(1000), [Status] VARCHAR(1000) NULL, [Login] VARCHAR(1000) NULL, HostName VARCHAR(1000) NULL, BlkBy VARCHAR(1000) NULL, DBName VARCHAR(1000) NULL, Command VARCHAR(1000) NULL, CPUTime VARCHAR(1000) NULL, DiskIO VARCHAR(1000) NULL, LastBatch VARCHAR(1000) NULL, ProgramName VARCHAR(1000) NULL, SPID2 VARCHAR(1000) NULL, Request VARCHAR(1000) NULL)
INSERT INTO @SPWHO2 
    EXEC sp_who2 'Active'
SELECT * FROM @SPWHO2 WHERE DBName = @dbName

score 8 · Answer 8 · answered Dec 11 '19 at 15:27

8

You can perform the following T-SQL command:

SELECT * FROM sys.dm_exec_sessions WHERE status = 'running';

answered Dec 11 '19 at 15:27

sinkmanu

1,034
1
12
24

How do I see active SQL Server connections?

8 Answers8

Linked