驗證SQLServer死鎖進程

SELECT '現在沒有阻塞和死鎖信息' AS message

-- 迴圈開始
WHILE @intCounter <= @intCountProperties
BEGIN
-- 取第一條記錄
SELECT @spid = spid ,
@bl = bl
FROM #tmp_lock_who
WHERE Id = @intCounter
BEGIN
IF @spid = 0
BEGIN
SELECT '引起資料庫死鎖的是: ' + CAST(@bl AS VARCHAR(10))
+ '進程號,其執行的SQL語法如下'
END
ELSE
BEGIN
SELECT '進程號SPID:' + CAST(@spid AS VARCHAR(10))
+ '其當前進程執行的SQL語法如下:'
DBCC INPUTBUFFER(@spid)
SELECT '進程號SPID：' + CAST(@spid AS VARCHAR(10)) + '被'
+ '進程號SPID：' + CAST(@bl AS VARCHAR(10))
+ '阻塞,其當前進程執行的SQL語法如下'
END
DBCC INPUTBUFFER (@bl)
END

-- 迴圈指針下移
SET @intCounter = @intCounter + 1
END
DROP TABLE #tmp_lock_who