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 杀掉被锁进程新闻热点
疑难解答
