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
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