查杀死锁

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