sqlserver数据库死锁查看

windy8848

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_who_lock]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_who_lock]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[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 master.dbo.sysprocesses where  blocked>0 ) a
where not EXISTS (select * from (select * from master.dbo.sysprocesses where  blocked>0) b where spid=a.blocked)
union
select spid,blocked from master.dbo.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

GO




IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_lockinfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_lockinfo]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

   
  CREATE proc [dbo].[sp_lockinfo]  
@kill_lock_spid bit=1,   --是否杀掉阻塞的进程,1：杀掉,0：仅显示  
@show_spid_if_nolock bit=1,  --如果没有阻塞的进程,是否显示正常进程信息,1：显示,0：不显示  
@dbname sysname=''    --如果为空,则查询所有的库,如果为null,则查询当前库,否则查询指定库  
  as  
   --邹建的过程
   --调用方法：  exec sp_lockinfo 0,0,'skmaster'
   
  set nocount on  
  declare @count int,
  @sql nvarchar(4000),
  @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 #tb
  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)  
  ) t
  order by s1,s2  
   
  select @count=@@rowcount  
   
  if @count=0 AND @show_spid_if_nolock=1  
  begin  
   insert #tb  
   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 #tt ( id INT identity(1,1), a nvarchar(30), b int, EventInfo nvarchar(255) )  
   declare tb cursor local  
   for  
   SELECT N'insert into #tt exec(''dbcc inputbuffer('+rtrim(进程ID)+')'')  
   if @@rowcount=0 insert into #tt(a) values(null)'  
   +case when @kill_lock_spid=1 and 标志=N'阻塞的进程' then 'kill '+rtrim(进程ID) else '' end  
   FROM #tb
   OPEN tb  
   fetch tb into @sql  
   while @@fetch_status=0  
   begin  
    exec(@sql)  
    fetch tb into @sql  
   end  
   close tb  
   deallocate tb  
   select a.*,进程的SQL语句=b.EventInfo  
   from #tb a join #tt b ON a.id=b.id  
   order by a.ID  
  end  
  
  set nocount off  
GO

naojin

貌似在sqlsever的管理/当前活动中也能看到

andyerp10

对我这样 的初学者来说，不太明白啊

kjkjkj123

不明白。。。。

dengxh

看不懂。。。。。。