Cari Blog Ini

22 Oktober 2012

How To Check Running Query


Apply for: Microsoft SQL Server 2008 R2

1. Query

-----code:start------------------------------------------------------------------------

SELECT P.spid
, RIGHT
(
CONVERT(VARCHAR
,DATEADD(ms, DATEDIFF(ms, P.last_batch, GETDATE()), '1900-01-01'), 121)
,12
)
  , P.program_name
  , P.hostname
  , P.loginame
  ,(SELECT text FROM ::fn_get_sql (sql_handle)) [Query]
  ,P.status
  ,P.cmd
FROM master.dbo.sysprocesses P WITH (NOLOCK)



WHERE  
P.spid > 50
    AND P.status NOT IN ('background', 'sleeping')
    AND P.cmd NOT IN
( 'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
)

-----code:end------------------------------------------------------------------------


2. Example Results Messages

Source: 
http://www.sqlservercentral.com/scripts/Performance/74593/
Article: Email Long Running SPID Info