数据库锁

 

/****** Object:  StoredProcedure [dbo].[SP_Sys_LockInfo]    Script Date: 12/20/2011 14:08:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--  SP_Sys_LockInfo 0,0  -- 仅显示锁住的SPID和被锁住的SPID

--  SP_Sys_LockInfo 1,0  -- 显示锁住的SPID并同时杀死它

CREATE proc [dbo].[SP_Sys_LockInfo]
@kill_lock_spid bit=0,   --是否杀掉锁死的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock bit=1 --如果没有锁死的进程,是否显示正常进程信息,1 显示,0 不显示
as
declare @count int,
 @s nvarchar(4000),
 @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

set @count=@@rowcount
set @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(3500))
 
 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

GO