| Create procedure sp_check_deadlock as
set nocount on
/*
select
spid
被鎖進程ID,
blocked 鎖進程ID,
status
被鎖狀態,
SUBSTRING(SUSER_SNAME(sid),1,30) 被鎖進程登陸帳號,
SUBSTRING(hostname,1,12)
被鎖進程使用者機器名稱,
SUBSTRING(DB_NAME(dbid),1,10)
被鎖進程資料名稱,
cmd 被鎖進程命令,
waittype 被鎖進程等待類型
FROM master..sysprocesses
WHERE blocked>0
--dbcc inputbuffer(66) 輸出相關鎖進程的語句
*/
--建立鎖進程暫存資料表
CREATE TABLE #templocktracestatus (
EventType
varchar(100),
Parameters INT,
EventInfo
varchar(200)
)
--建立被鎖進程暫存資料表
CREATE TABLE #tempbelocktracestatus (
EventType
varchar(100),
Parameters INT,
EventInfo
varchar(200)
) --建立之間的關聯表
CREATE TABLE #locktracestatus (
belockspid INT,
belockspidremark varchar(20),
belockEventType
varchar(100),
belockEventInfo
varchar(200),
lockspid INT,
lockspidremark
varchar(20),
lockEventType
varchar(100),
lockEventInfo
varchar(200)
)
--擷取死結進程
DECLARE dbcc_inputbuffer CURSOR READ_ONLY
FOR select spid 被鎖進程ID,blocked 鎖進程ID
FROM master..sysprocesses
WHERE blocked>0
DECLARE @lockedspid int
DECLARE @belockedspid int
OPEN dbcc_inputbuffer
FETCH NEXT FROM dbcc_inputbuffer INTO @belockedspid,@lockedspid
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
--print '被堵塞進程'
--select @belockedspid
--dbcc inputbuffer(@belockedspid)
--print '堵塞進程'
--select @lockedspid
--dbcc inputbuffer(@lockedspid)
INSERT INTO #tempbelocktracestatus
EXEC('DBCC INPUTBUFFER('+@belockedspid+')')
INSERT INTO #templocktracestatus
EXEC('DBCC INPUTBUFFER('+@lockedspid+')')
INSERT INTO #locktracestatus
select @belockedspid,'被鎖進程',a.EventType,a.EventInfo,@lockedspid,'鎖進程',b.EventType,b.EventInfo
from #tempbelocktracestatus a,#templocktracestatus b
END
FETCH NEXT FROM dbcc_inputbuffer INTO @belockedspid,@lockedspid
END
CLOSE dbcc_inputbuffer
DEALLOCATE dbcc_inputbuffer
select * from #locktracestatus
return (0) -- sp_check_deadlock
|
