/*-----------------------------------------------------------------------------------------------------------------------
名 稱:查看死锁情況
調用對像:
備註說明:
修改日志:
程式作者:XXXXX
-----------------------------------------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[sp_who_lock]
AS
BEGIN
SET NOCOUNT ON
DECLARE @spid int,@bl int,@intTransactionCountOnEntry int,@intRowcount int,@intCountProperties int,@intCounter int
CREATE TABLE #tmp_lock_who
(
id int identity(1,1),
spid smallint,
bl smallint
)
IF @@ERROR<>0 RETURN @@ERROR
INSERT INTO #tmp_lock_who(spid,bl)
SELECT 0 ,blocked
FROM (SELECT * FROM sysprocesses WHERE blocked>0 ) a
WHERE not exists(SELECT * FROM (SELECT * FROM sysprocesses WHERE blocked>0 ) b
WHERE a.blocked=spid)
UNION SELECT spid,blocked FROM sysprocesses WHERE blocked>0
IF @@ERROR<>0 RETURN @@ERROR
--找到臨時表的記錄數
SELECT @intCountProperties = Count(*),@intCounter = 1 FROM #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
IF @intCountProperties=0
SELECT '現在沒有阻塞和死鎖信息' as Message
--循環開始
WHILE @intCounter <= @intCountProperties
BEGIN
--取第一條記錄
SELECT @spid = spid,@bl = bl
FROM #tmp_lock_who WHERE Id = @intCounter
BEGIN
IF @spid =0
SELECT '引起數據庫死鎖的是: '+ CAST(@bl AS VARCHAR(10)) + '進程號,其執行的SQL語法如下'
ELSE
SELECT '進程號SPID:'+ CAST(@spid AS VARCHAR(10))+ '被' + '進程號SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其當前進程執行的SQL語法如下'
DBCC INPUTBUFFER (@bl )
END
--循環指針下移
SET @intCounter = @intCounter + 1
END
DROP TABLE #tmp_lock_who
RETURN 0
SET NOCOUNT OFF
END
GO