CODEWITHSUNDEEP
sqlsql-serversql-server-2008
George2
George2

Reputation: 45801

How do I see active SQL Server connections?

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?

Upvotes: 420

Views: 1617075

Answers (8)

Marcello Miorelli
Marcello Miorelli

Reputation: 3698

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'

Upvotes: 55

Fernando Santos
Fernando Santos

Reputation: 649

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)

Upvotes: 64

Zalakain
Zalakain

Reputation: 606

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;

Upvotes: 22

sinkmanu
sinkmanu

Reputation: 1112

You can perform the following T-SQL command:

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

Upvotes: 10

Mehrdad Afshari
Mehrdad Afshari

Reputation: 422252

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.

Upvotes: 444

Sklivvz
Sklivvz

Reputation: 31163

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.

Upvotes: 71

Syed Umar Ahmed
Syed Umar Ahmed

Reputation: 5962

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.

Upvotes: 477

Don Rolling
Don Rolling

Reputation: 2349

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

Upvotes: 16

Related Questions