Troubleshooting Blocked Transaction in SQL Server

Posted by ChrisD on Geeks with Blogs See other posts from Geeks with Blogs or by ChrisD
Published on Fri, 07 Dec 2012 21:45:00 GMT Indexed on 2012/12/07 17:08 UTC
Read the original article Hit count: 253

Filed under:

While troubleshooting a blocked transaction issue recently, I found this code online.  My apologies in not citing its source, but its lost in my browse history some where.

 

While the transaction is executing and blocked, open a connection to the database containing the transaction and run the following to return both the SQL statement blocked (the Victim), as well as the statement that’s causing the block (the Culprit)

 

--

prepare a table so that we can filter out sp_who2 results

DECLARE @who TABLE(BlockedId INT,

Status VARCHAR(MAX),

LOGIN VARCHAR(MAX),

                   HostName VARCHAR(MAX),

                   BlockedById VARCHAR(MAX),

                   DBName VARCHAR(MAX),

                   Command VARCHAR(MAX),

                   CPUTime INT,

                   DiskIO INT,

                   LastBatch VARCHAR(MAX),

                   ProgramName VARCHAR(MAX),

                   SPID_1 INT,

                   REQUESTID INT)

INSERT INTO @who EXEC sp_who2

--select the blocked and blocking queries (if any) as SQL text

SELECT

(

SELECT TEXT

FROM sys.dm_exec_sql_text(

(SELECT handle 

FROM (

SELECT CAST(sql_handle AS VARBINARY(128)) AS handle

FROM sys.sysprocesses WHERE spid = BlockedId

) query)

)

) AS 'Blocked Query (Victim)',

(

SELECT TEXT

FROM sys.dm_exec_sql_text(

(SELECT handle 

FROM (

SELECT CAST(sql_handle AS VARBINARY(128)) AS handle

FROM sys.sysprocesses WHERE spid = BlockedById

) query)

)

) AS 'Blocking Query (Culprit)'

FROM @who 

WHERE BlockedById != '  .'

© Geeks with Blogs or respective owner