What Was I Thinking?

Follies & Foils of .NET Development
posts - 87 , comments - 254 , trackbacks - 0

Troubleshooting Blocked Transaction in SQL Server

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 != '  .'

Print | posted on Friday, December 7, 2012 4:45 PM | Filed Under [ SQL ]

Feedback

No comments posted yet.
Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 
 

Powered by: