USE [master]
GO
/****** Object:  StoredProcedure [dbo].[p_lockinfo]    Script Date: 02/07/2014 11:54:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[usp_lockinfo]
@kill_lock_spid BIT=0,  --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock BIT=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
AS
BEGIN
    DECLARE @count INT,@s NVARCHAR(1000),@i INT
    SELECT id=IDENTITY(INT,1,1),标志,
     进程ID=SPID,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,
     数据库名=DB_NAME(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,
     登陆时间=login_time,打开事务数=open_tran, 进程状态=status,
     工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,
     域名=nt_domain,网卡地址=net_address
    INTO #t FROM(
     SELECT 标志=‘死锁的进程‘,
      SPID,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
      status,hostname,program_name,hostprocess,nt_domain,net_address,
      s1=a.spid,s2=0
     FROM master..sysprocesses a JOIN (
      SELECT blocked FROM master..sysprocesses GROUP BY blocked
      )b ON a.spid=b.blocked WHERE a.blocked=0
     UNION ALL
     SELECT ‘|_牺牲品_>‘,
      SPID,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
      status,hostname,program_name,hostprocess,nt_domain,net_address,
      s1=blocked,s2=1
     FROM master..sysprocesses a WHERE blocked<>0
    )a ORDER BY s1,s2

    SELECT @count=@@ROWCOUNT,@i=1

    IF @count=0 AND @show_spid_if_nolock=1
    BEGIN
     INSERT #t
     SELECT 标志=‘正常的进程‘,
      SPID,kpid,blocked,dbid,DB_NAME(dbid),uid,loginame,cpu,login_time,
      open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
     FROM master..sysprocesses
     SET @count=@@ROWCOUNT
    END

    IF @count>0
    BEGIN
     CREATE TABLE #t1(id INT IDENTITY(1,1),a NVARCHAR(30),b INT,EventInfo NVARCHAR(MAX))
     IF @kill_lock_spid=1
     BEGIN
      DECLARE @spid VARCHAR(10),@标志 VARCHAR(10)
      WHILE @i<=@count
      BEGIN
       SELECT @spid=进程ID,@标志=标志 FROM #t WHERE id=@i
       INSERT #t1 EXEC(‘dbcc inputbuffer(‘+@spid+‘)‘)
       IF @标志=‘死锁的进程‘ EXEC(‘kill ‘+@spid)
       SET @i=@i+1
      END
     END
     ELSE
      WHILE @i<=@count
      BEGIN
       SELECT @s=‘dbcc inputbuffer(‘+CAST(进程ID AS VARCHAR)+‘)‘ FROM #t WHERE id=@i
       INSERT #t1 EXEC(@s)
       SET @i=@i+1
      END
     SELECT a.*,进程的SQL语句=b.EventInfo
     FROM #t a JOIN #t1 b ON a.id=b.id
    END
END
GO
--=====================================================
--Usage
EXEC [master].[dbo].[usp_lockinfo]
@kill_lock_spid=0,  --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示