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