MSSQL查找进程造成死锁_把这个进程杀掉

 MSSQL(查找死锁)：  
   
  use   master    
  go    
  declare   @spid   int,@bl   int    
  DECLARE   s_cur   CURSOR   FOR      
  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    
  OPEN   s_cur    
  FETCH   NEXT   FROM   s_cur   INTO   @spid,@bl    
  WHILE   @@FETCH_STATUS   =   0    
  begin    
  if   @spid   =0      
                          select   '引起数据库死锁的是:   '+   CAST(@bl   AS   VARCHAR(10))   +   '进程号,其执行的SQL语法如下'    
  else    
                          select   '进程号SPID：'+   CAST(@spid   AS   VARCHAR(10))+   '被'   +   '进程号SPID：'+   CAST(@bl   AS   VARCHAR(10))   +'阻塞,其当前进程执行的SQL语法如下'    
  DBCC   INPUTBUFFER   (@bl   )    
  FETCH   NEXT   FROM   s_cur   INTO   @spid,@bl    
  end    
  CLOSE   s_cur    
  DEALLOCATE   s_cur    
   
   
   
   
  --邹建的  
  create   proc   sp_lockinfo  
  @kill_lock_spid   bit=1,             --是否杀掉阻塞的进程,1   杀掉,   0   仅显示  
  @show_spid_if_nolock   bit=1,   --如果没有阻塞的进程,是否显示正常进程信息,1   显示,0   不显示  
  @dbname   sysname=''                     --如果为空,则查询所有的库,如果为null,则查询当前库,否则查询指定库  
  as  
  set   nocount   on  
  declare   @count   int,@s   nvarchar(2000),@dbid   int  
  if   @dbname=''   set   @dbid=db_id()   else   set   @dbid=db_id(@dbname)  
   
  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    
  where   blocked>0  
  and(@dbid   is   null   or   dbid=@dbid)  
  group   by   blocked  
  )b   on   a.spid=b.blocked    
  where   a.blocked=0  
  and(@dbid   is   null   or   dbid=@dbid)  
  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=spid  
  from   master..sysprocesses   a    
  where   blocked<>0  
  and(@dbid   is   null   or   dbid=@dbid)  
  )a   order   by   s1,s2  
   
  select   @count=@@rowcount  
   
  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  
  where   @dbid   is   null   or   dbid=@dbid  
  order   by   spid  
  set   @count=@@rowcount  
  end  
   
  if   @count>0  
  begin  
  create   table   #t1(id   int   identity(1,1),a   nvarchar(30),b   Int,EventInfo   nvarchar(255))  
  declare   tb   cursor   local  
  for  
  select   N'insert   #t1   exec(''dbcc   inputbuffer('+rtrim(进程ID)+')'')  
  if   @@rowcount=0   insert   #t1(a)   values(null)  
  '+case   when   @kill_lock_spid=1   and   标志=N'阻塞的进程'  
  then   'kill   '+rtrim(进程ID)   else   ''   end  
  from   #t  
  open   tb  
  fetch   tb   into   @s  
  while   @@fetch_status=0  
  begin  
  exec(@s)  
  fetch   tb   into   @s  
  end  
  close   tb  
  deallocate   tb  
  select   a.*,进程的SQL语句=b.EventInfo  
  from   #t   a   join   #t1   b   on   a.id=b.id  
  order   by   a.ID  
  end  
  set   nocount   off  
  go   

-----------------------------------------------------------------------

修改一下:

SQL Server数据库发生死锁时不会像ORACLE那样自动生成一个跟踪文件。有时可以在[管理]->[当前活动] 里看到阻塞信息(有时SQL Server企业管理器会因为锁太多而没有响应).

　　设定跟踪1204:

USE MASTER
DBCC TRACEON (1204,-1)

　　显示当前启用的所有跟踪标记的状态:

DBCC TRACESTATUS(-1)

　　取消跟踪1204:

DBCC TRACEOFF (1204,-1)

　　在设定跟踪1204后,会在数据库的日志文件里显示SQL Server数据库死锁时一些信息。但那些信息很难看懂,需要对照SQL Server联机丛书仔细来看。根据PAG锁要找到相关数据库表的方法:

DBCC TRACEON (3604)
DBCC PAGE (db_id,file_id,page_no)
DBCC TRACEOFF (3604)

　　请参考sqlservercentral.com上更详细的讲解.但又从CSDN学到了一个找到死锁原因的方法。我稍加修改, 去掉了游标操作并增加了一些提示信息,写了一个系统存储过程sp_who_lock.sql。代码如下：

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[sp_who_lock]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_who_lock]
GO
/********************************************************
//  学习到并改写
//  说明 : 查看数据库里阻塞和死锁情况
********************************************************/
use master
go
create procedure sp_who_lock
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry     int,
@intRowcount             int,
@intCountProperties         int,
@intCounter             int
create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
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 '现在没有阻塞和死锁信息' as message
-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
select     @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被'
+ '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end
-- 循环指针下移
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_who
return 0
end

　　需要的时候直接调用:

sp_who_lock

　　就可以查出引起死锁的进程和SQL语句.

　　SQL Server自带的系统存储过程sp_who和sp_lock也可以用来查找阻塞和死锁, 但没有这里介绍的方法好用。如果想知道其它tracenum参数的含义,请看http://www.sqlservercentral.com/文章

　　我们还可以设置锁的超时时间(单位是毫秒), 来缩短死锁可能影响的时间范围:

　　例如:

use master
seelct @@lock_timeout
set lock_timeout 900000
-- 15分钟
seelct @@lock_timeout