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

User · Answer

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

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

User · Answer

SELECT      DB NAME dbid  as DBName       COUNT dbid  as NumberOfConnections      loginame as LoginName FROM     sys sysprocesses WHERE      dbid  gt  0 GROUP BY      dbid  loginame     See also the Microsoft documentation for sys sysprocesses

User · Answer

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

User · Answer

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

User · Answer

You can perform the following T-SQL command   SELECT   FROM sys dm exec sessions WHERE status    running

User · Answer

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  gt  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  gt  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

User · Answer

Click the  quot activity monitor quot  icon in the toolbar  From Thorsten s comment  In SQL Server Management Studio  right click on Server  choose  quot Activity Monitor quot  from context menu -or- use keyboard shortcut Ctrl   Alt   A  Reference  Microsoft Docs - Open Activity Monitor in SQL Server Management Studio  SSMS

User · Answer

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

[sql] How do I see active SQL Server connections?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008