USE [master]GO/****** Object:  StoredProcedure [dbo].[p_lockinfo]    Script Date: 02/07/2014 11:54:29 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROC [dbo].[usp_lockinfo]@kill_lock_spid BIT=0,  --是否杀掉死锁的进程,1 杀掉, 0 仅显示@show_spid_if_nolock BIT=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示ASBEGIN    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    ENDENDGO--=====================================================--UsageEXEC [master].[dbo].[usp_lockinfo]@kill_lock_spid=0,  --是否杀掉死锁的进程,1 杀掉, 0 仅显示@show_spid_if_nolock=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示