abstract：USE [DataBaseName]GO/****** Object:  StoredProcedure [dbo].[sp_check_deadlock]    Script Date: 07/04/2014 13:49:30 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedu

USE [DataBaseName]
GO
/****** Object:  StoredProcedure [dbo].[sp_check_deadlock]    Script Date: 07/04/2014 13:49:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[sp_check_deadlock]

  as
set nocount on

  /*
  SELECT 
  spid As 被鎖進(jìn)程ID,
  blocked As 鎖進(jìn)程ID, 
  status  As 被鎖狀態(tài), 
  SUBSTRING(SUSER_SNAME(sid),1,30) 被鎖進(jìn)程登陸帳號, 
  SUBSTRING(hostname,1,12) As 被鎖進(jìn)程用戶機器名稱, 
  SUBSTRING(DB_NAME(dbid),1,10) As 被鎖進(jìn)程數(shù)據(jù)名稱, 
  cmd 被鎖進(jìn)程命令, 
  waittype 被鎖進(jìn)程等待類型
  FROM master..sysprocesses 
  WHERE blocked>0 
   
  --dbcc inputbuffer(66) 輸出相關(guān)鎖進(jìn)程的語句

  -- kill 24   殺掉被鎖進(jìn)程
   */
   
  --創(chuàng)建鎖進(jìn)程臨時表
  CREATE TABLE #templocktracestatus (  
          EventType varchar(max),  
          Parameters INT,  
          EventInfo varchar(max)  
          )
  --創(chuàng)建被鎖進(jìn)程臨時表
  CREATE TABLE #tempbelocktracestatus (  
          EventType varchar(max),  
          Parameters INT,
          EventInfo varchar(max)  
          )

--創(chuàng)建之間的關(guān)聯(lián)表
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)  
        )
   
  --獲取死鎖進(jìn)程
  DECLARE dbcc_inputbuffer CURSOR READ_ONLY
 FOR SELECT spid 被鎖進(jìn)程ID,blocked 鎖進(jìn)程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 '被堵塞進(jìn)程'  
--SELECT @belockedspid  
--dbcc inputbuffer(@belockedspid)  
--print '堵塞進(jìn)程'  
--SELECT @lockedspid  
--dbcc inputbuffer(@lockedspid)
   INSERT INTO #tempbelocktracestatus  
   EXEC('DBCC INPUTBUFFER('+@belockedspid+')')
  
   INSERT INTO #templocktracestatus   
   EXEC('DBCC INPUTBUFFER('+@lockedspid+')')
  
   INSERT INTO #locktracestatus  
   SELECT @belockedspid,'被鎖進(jìn)程',a.EventType,a.EventInfo,@lockedspid,'鎖進(jìn)程',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
  -- sp_who

  -- kill 24   殺掉被鎖進(jìn)程