1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

-- List down all the blocking process or root blockers
SELECT  DISTINCT p1.spid  AS [Blocking/Root Blocker SPID]
        , p1.[loginame] AS [RootBlocker_Login]
    , st.text AS [SQL Query Text]
        , p1.[CPU]
        , p1.[Physical_IO]
        , DB_NAME(p1.[dbid]) AS DBName
        , p1.[Program_name]
        , p1.[HostName]
        , p1.[Status]
        , p1.[CMD]
        , p1.[Blocked]
        , p1.[ECID] AS [ExecutionContextID]
FROM  sys.sysprocesses p1
INNER JOIN  sys.sysprocesses p2 ON p1.spid = p2.blocked AND p1.ecid = p2.ecid 
CROSS APPLY sys.dm_exec_sql_text(p1.sql_handle) st
WHERE p1.blocked = 0 
ORDER BY p1.spid, p1.ecid
-- List Down all the blocked processes
SELECT p2.spid AS 'Blocked SPID'
        , p2.blocked AS 'Blocking/Root Blocker SPID'
        , p2.[loginame] AS [BlockedSPID_Login]
        ,  st.text AS [SQL Query Text]
        , p2.[CPU]
        , p2.[Physical_IO]
        , DB_NAME(p2.[dbid]) AS DBName
        , p2.[Program_name]
        , p2.[HostName]
        , p2.[Status]
        , p2.[CMD]
        , p2.ECID AS [ExecutionContextID]
FROM sys.sysprocesses p1 
INNER JOIN sys.sysprocesses p2 ON p1.spid = p2.blocked AND p1.ecid = p2.ecid
CROSS APPLY sys.dm_exec_sql_text(p1.sql_handle) st

1

DBCC INPUTBUFFER(<SPID>)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

DECLARE @SPID int;
SET @SPID = <SPID>

SELECT SDER.[statement_start_offset],  
  SDER.[statement_end_offset], 
  CASE  
     WHEN SDER.[statement_start_offset] > 0 THEN 
        --The start of the active command is not at the beginning of the full command text
        CASE SDER.[statement_end_offset] 
           WHEN -1 THEN 
              --The end of the full command is also the end of the active statement
              SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, 2147483647)
           ELSE  
              --The end of the active statement is not at the end of the full command
              SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, (SDER.[statement_end_offset] - SDER.[statement_start_offset])/2)  
        END 
     ELSE 
        --1st part of full command is running
        CASE SDER.[statement_end_offset] 
           WHEN -1 THEN 
              --The end of the full command is also the end of the active statement
              RTRIM(LTRIM(DEST.[text])) 
           ELSE 
              --The end of the active statement is not at the end of the full command
              LEFT(DEST.TEXT, (SDER.[statement_end_offset]/2) +1) 
        END 
     END AS [executing statement], 
  DEST.[text] AS [full statement code] 
FROM sys.[dm_exec_requests] SDER CROSS APPLY sys.[dm_exec_sql_text](SDER.[sql_handle]) DEST 
WHERE SDER.session_id = @SPID
ORDER BY SDER.[session_id], SDER.[request_id]