USE [DataBaseName]GO/****** Object: StoredPRocedure [dbo].[sp_check_deadlock] Script Date: 07/04/2014 13:49:30 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO
ALTER procedure [dbo].[sp_check_deadlock] asset nocount on /* select spid As 被鎖進程ID, blocked As 鎖進程ID, status As 被鎖狀態, SUBSTRING(SUSER_SNAME(sid),1,30) 被鎖進程登陸帳號, SUBSTRING(hostname,1,12) As 被鎖進程用戶機器名稱, SUBSTRING(DB_NAME(dbid),1,10) As 被鎖進程數據名稱, cmd 被鎖進程命令, waittype 被鎖進程等待類型 FROM master..sysprocesses WHERE blocked>0 --dbcc inputbuffer(66) 輸出相關鎖進程的語句 -- kill 24 殺掉被鎖進程 */ --創建鎖進程臨時表 CREATE TABLE #templocktracestatus ( EventType varchar(max), Parameters INT, EventInfo varchar(max) ) --創建被鎖進程臨時表 CREATE TABLE #tempbelocktracestatus ( EventType varchar(max), Parameters INT,EventInfo varchar(max) )--創建之間的關聯表CREATE TABLE #locktracestatus ( belockspid INT, belockspidremark varchar(max), belockEventType varchar(max), belockEventInfo varchar(max), lockspid INT, lockspidremark varchar(max), lockEventType varchar(max), lockEventInfo varchar(max) ) --獲取死鎖進程 DECLARE dbcc_inputbuffer CURSOR READ_ONLYFOR 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 bEND FETCH NEXT FROM dbcc_inputbuffer INTO @belockedspid,@lockedspid END CLOSE dbcc_inputbuffer DEALLOCATE dbcc_inputbuffer select * from #locktracestatus return (0) -- sp_check_deadlock -- sp_who -- kill 24 殺掉被鎖進程新聞熱點
疑難解答
