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

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

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

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

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

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

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 perform the following T-SQL command   SELECT   FROM sys dm exec sessions WHERE status    running

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  lt  gt    SPID   AND sdest DatabaseName   yourdatabasename  --ORDER BY sdes last request start time DESC  --

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008