编写的一个存储过程，来检测数据库死锁进程

 /*--------------------------------------------------------------------------------------- 
 
名称：sp_who_lock 
功能：用来检测是否有死锁的进程，如果有，把造成死锁的进程ID,HostName, ProgramName 和执行的SQL 
      语句保存到表jobcn_boss_operlog.dbo.DeadLockLog 
 
参数: @SaveToTable 用来标示是否保存死锁进程到表jobcn_boss_operlog.dbo.DeadLockLog 
 1 是， 0 否， 默认 = 0 
 
     @PrintRootCauseOnly 是否只显示造成死锁的根源进程信息 
        1 只显示死锁根源进程 0 除显示死锁根源进程外，还显示被阻塞的进程， 默认 = 0  
 
时间：2010-10-11 
版本：1.0 
修改： 
 
---------------------------------------------------------------------------------------*/ 
 
CREATE  procedure dbo.sp_who_lock 
 @SaveToTable int = 0, 
 @PrintRootCauseOnly int = 1 
as 
begin 
 
set nocount on 
declare @spid int,@bl int, 
 @intTransactionCountOnEntry int, 
        @intRowcount int, 
        @intCountProperties int, 
        @intCounter int, 
 @HostName nchar(128), 
 @ProgramName nchar(128), 
 @SQLCmd as varchar(1000) 
  
create table #tmp_lock_who ( 
 id int identity(1,1), 
 spid smallint, 
 bl smallint) 
 
create table #tmpSQL (EventType varchar(100), 
 Parameters int, 
 EventInfo varchar(2000)) 
 
  
IF @@ERROR<>0  
 RETURN @@ERROR 
  
insert into #tmp_lock_who(spid,bl)  
 select 0 ,blocked 
  from (select * from sysprocesses where blocked>0 ) a 
  where not exists(select * from (select * from sysprocesses where blocked>0 ) b 
    where a.blocked=spid) 
 union select spid,blocked from sysprocesses where blocked>0 
  
IF @@ERROR<>0  
 RETURN @@ERROR 
  
-- 找到临时表的记录数 
select @intCountProperties = Count(*),@intCounter = 1 
 from #tmp_lock_who 
  
IF @@ERROR<>0  
 RETURN @@ERROR 
  
if @intCountProperties=0 
 select N'现在没有阻塞和死锁信息' as message 
-- 循环开始 
 
while @intCounter <= @intCountProperties 
begin 
 -- 取第一条记录 
 select @spid = spid,@bl = bl 
  from #tmp_lock_who where Id = @intCounter 
 begin 
  if @spid =0 
          print N'* 引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + N'进程号,其执行的SQL语法如下' 
  else 
   begin 
    if @PrintRootCauseOnly <> 1 
            print N'进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ N'被' + N'进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +N'阻塞,其当前进程执行的SQL语法如下' 
   end 
 
  --Get bolocker's host name and program name 
  select @HostName = hostname, @ProgramName = program_name  
   from sysprocesses  
   where SPID = @bl 
 
  truncate table #tmpSQL 
  select @SQLCmd = 'dbcc inputbuffer(' + cast(@bl as varchar(10)) + ') with no_infomsgs' 
  insert into  #tmpSQL exec(@SQLCmd) 
   
  select @SQLCmd = EventInfo from #tmpSQL 
 
  if @PrintRootCauseOnly <> 1 or @spid =0 
  begin 
   print @SQLCmd 
   print ' ' 
  end 
 
  if @SaveToTable = 1 and @spid =0   --写入引起数据库死锁的进程号到jobcn_boss_operlog.dbo.DeadLockLog 
   insert into jobcn_boss_operlog.dbo.DeadLockLog  
    (SPID,HostName,ProgramName,SQLExec,FindDate) values 
    (@bl,@HostName,@ProgramName,@SQLCmd,getdate()) 
 
   
   --DBCC INPUTBUFFER (@bl ) 
 end 
  
 -- 循环指针下移 
 set @intCounter = @intCounter + 1 
end 
  
  
drop table #tmp_lock_who 
drop table #tmpSQL 
 
set nocount off 
  
return 0 
 
end