查杀死锁

Create Proc [dbo].[p_lockinfo]
          @kill_lock_spid      Bit = 1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示
          @show_spid_if_nolock Bit = 1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
                                     
As
  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(255))
      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

Create Proc [dbo].[p_killspid]
           @dbname Varchar(200) --要关闭进程的数据库名
           
As
  Declare
    @sql Nvarchar(500)
  Declare
    @spid Nvarchar(20)
  Declare #tb Cursor  For
  Select spid = Cast(spid As Varchar(20))
  From   master..sysprocesses
  Where  dbid = Db_id(@dbname)
  Open #tb
  Fetch Next From #tb
  Into @spid
  While @@fetch_status = 0
    Begin
      Exec( 'kill ' + @spid)
      Fetch Next From #tb
      Into @spid
    End
  Close #tb
  Deallocate #tb