sys.sysprocesses

• Divine Flame

  SSCoach

  Points: 15943

  More actions

  February 29, 2012 at 6:33 am

  #1453128

  sqlnaive (2/29/2012)

  ---

  select a.spid, b.text from sys.sysprocesses a cross apply sys.dm_exec_sql_text(a.sql_handle) b

  This is the query I'm using. This is giving me even those sessions which are closed in query analyzer.

  May be this can help:

  SELECT SPC.[spid],DEST.[text]

  FROM

  sys.sysprocesses SPC CROSS APPLY sys.dm_exec_sql_text(SPC.[sql_handle]) DEST

  WHERE

  LOWER(SPC.[status]) <> 'sleeping'

  AND

  UPPER(SPC.cmd) NOT IN ('AWAITING COMMAND','LAZY WRITER','CHECKPOINT SLEEP')

  ---

  Sujeet Singh

• Gazareth

  One Orange Chip

  Points: 27737

  More actions

  February 29, 2012 at 7:14 am

  #1453144

  Isn't sys.sysprocesses marked for deprecation?

  I use sys.dm_exec_requests/connections/sessions (whichever's appropriate) in its place.

• Divine Flame

  SSCoach

  Points: 15943

  More actions

  February 29, 2012 at 7:46 am

  #1453174

  Gazareth (2/29/2012)

  ---

  Isn't sys.sysprocesses marked for deprecation?

  I use sys.dm_exec_requests/connections/sessions (whichever's appropriate) in its place.

  Yes, It is marked for deprecation. Most of us have already started using sys.dm_exec_sessions & sys.dm_exec_requests instead. However, I believe that OP is already aware about that.

  ---

  Sujeet Singh