Wednesday, March 19, 2014

What happens when a transaction dies a terrible blocking death in SQL Server

USE <DATABASE>

DECLARE @UoWID uniqueidentifier
SET @UoWID = (
select TOP 1 req_transactionUoW as [UoW ID] from sys.syslockinfo where req_spid = -2)

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'KILL ''' + CAST(@UoWID AS VARCHAR(300)) + ''''

SELECT @SQL
EXEC sp_executesql @SQL

REFS:
http://www.sqlskills.com/blogs/paul/disaster-recovery-101-dealing-with-negative-spids-2-and-3/
http://www.eraofdata.com/orphaned-msdtc-transactions-2-spids/

No comments:

Post a Comment