查看锁信息、强制杀锁

骑猪看日落 2022-06-18 06:06 195阅读 0赞

\--源自 ：[http://support.microsoft.com/kb/308518/zh-cn][http_support.microsoft.com_kb_308518_zh-cn]

刷新**当前活动**将调用 **sp\_MSset\_current\_activity** 存储过程。 在定义该存储过程时，首先将 *lock\_timeout*变量设置为 5000 毫秒 (ms)。 然后，会创建两个全局临时表，它们针对下面的系统表在表级别发出 IX 锁，在键级别发出 X 锁：

*  **tempdb..sysobjects** 
 *  **tempdb..sysindexes** 
 *  **tempdb..syscolumns**

如果在 5000 毫秒 (ms) 内未将前面的锁授予该进程，则查询将停止，并出现“症状”一节中显示的错误信息。  
  
再刷新一次通常会成功。 但是，如果保持这两个锁的时间太长，则也会显示该错误信息。 如果执行 **sp\_lock** 存储过程，则可计算出哪个进程正在对 **dbid**2 (**tempdb**) 的对象 ID 1、2 和 3 保持锁定。

查看锁信息：  
转载自：[http://topic.csdn.net/t/20040109/16/2650137.html][http_topic.csdn.net_t_20040109_16_2650137.html]

\--查看锁信息  
create   table   \#t(req\_spid   int,obj\_name   sysname)  
  
declare   @s   nvarchar(4000)  
,@rid   int,@dbname   sysname,@id   int,@objname   sysname  
  
declare   tb   cursor   for    
select   distinct   req\_spid,dbname=db\_name(rsc\_dbid),rsc\_objid  
from   master..syslockinfo   where   rsc\_type   in(4,5)  
open   tb  
fetch   next   from   tb   into   @rid,@dbname,@id  
while   @@fetch\_status=0  
begin  
set   @s= 'select   @objname=name   from   \[ '+@dbname+ '\]..sysobjects   where   id=@id '  
exec   sp\_executesql   @s,N '@objname   sysname   out,@id   int ',@objname   out,@id  
insert   into   \#t   values(@rid,@objname)  
fetch   next   from   tb   into   @rid,@dbname,@id  
end  
close   tb  
deallocate   tb  
  
select   进程id=a.req\_spid  
,数据库=db\_name(rsc\_dbid)  
,类型=case   rsc\_type   when   1   then   'NULL   资源（未使用） '  
when   2   then   '数据库 '  
when   3   then   '文件 '  
when   4   then   '索引 '  
when   5   then   '表 '  
when   6   then   '页 '  
when   7   then   '键 '  
when   8   then   '扩展盘区 '  
when   9   then   'RID（行   ID) '  
when   10   then   '应用程序 '  
end  
,对象id=rsc\_objid  
,对象名=b.obj\_name  
,rsc\_indid  
  from   master..syslockinfo   a   left   join   \#t   b   on   a.req\_spid=b.req\_spid  
  
go  
drop   table   \#t

转载自：[http://topic.csdn.net/u/20080526/16/f6e18b4d-876a-4ac1-bf1f-c359a6786d3b.html][http_topic.csdn.net_u_20080526_16_f6e18b4d-876a-4ac1-bf1f-c359a6786d3b.html]

杀死锁：

/\*--处理死锁

查看当前进程,或死锁进程,并能自动杀掉死进程

因为是针对死的,所以如果有死锁进程,只能查看死锁进程  
 当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程

\--邹建 2004.4--\*/

/\*--调用示例

exec p\_lockinfo  
\--\*/  
create proc 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][count]  
  begin  
   select @spid=进程ID,@标志=标志 from \#t where [id=@i][id_i]  
   insert \#t1 exec('dbcc inputbuffer(['+@spid+')'][spid])  
   if @标志='死锁的进程' exec('kill ['+@spid][spid])  
   set @i=@i+1  
  end  
 end  
 else  
  while @i<[=@count][count]  
  begin  
   select @s='dbcc inputbuffer('+cast(进程ID as varchar)+')' from \#t where [ id=@i][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

杀死锁：

转载自：[http://topic.csdn.net/t/20030922/10/2285767.html][http_topic.csdn.net_t_20030922_10_2285767.html]

\--杀除发起阻塞的进程  
\--用法：sp\_killblockingprocess   waittime     其中waittime单位是毫秒，为已经阻塞的时间  
\--如：杀除发起阻塞已经有40秒的进程   exec   sp\_killblockingprocess  
\--注意：必须将该过程放在master库中  
CREATE   PROCEDURE   sp\_killblockingprocess  
@waittime   int   =20000  
as  
DECLARE   @spid   smallint,@waitt   int,@killp   varchar(20)  
declare   cblocked   CURSOR   FOR  
select   spid,waittime   from   sysprocesses   where   blocked=0     and   open\_tran> 0   and   waittime> @waittime  
and   spid   in   (select   blocked   from   sysprocesses   where   blocked   > 0   )  
open   cblocked  
FETCH   NEXT   FROM   cblocked   into   @spid,@waitt  
WHILE   @@FETCH\_STATUS   =   0  
BEGIN  
  --kill   @spid  
  select   @killp= "KILL   "+convert(char(10),@spid)  
  print   @killp  
  exec   sp\_sqlexec   @killp  
  FETCH   NEXT   FROM   cblocked   into   @spid,@waitt  
end  
CLOSE   cblocked  
DEALLOCATE   cblocked

**杀掉相应数据库的进程**

转载自：[http://www.cnblogs.com/LCX/archive/2008/12/03/1346924.html][http_www.cnblogs.com_LCX_archive_2008_12_03_1346924.html]

Create Proc Sp\_KillAllProcessInDB

@DbName VarChar(100)

if db\_id(@DbName) = Null

begin

Print 'DataBase dose not Exist'

end

else

Begin

Declare @spId Varchar(30)

DECLARE TmpCursor CURSOR FOR

Select 'Kill ' + convert(Varchar, spid) as spId

from master..SysProcesses

where db\_Name(dbID) = @DbName

and spId <> @@SpId

and dbID <> 0

OPEN TmpCursor

FETCH NEXT FROM TmpCursor

INTO @spId

WHILE @@FETCH\_STATUS = 0

BEGIN

Exec (@spId)

FETCH NEXT FROM TmpCursor

INTO @spId

END

CLOSE TmpCursor

DEALLOCATE TmpCursor

end

\--To Execute

Exec dbo.Sp\_KillAllProcessInDB 'DBname'

[http_support.microsoft.com_kb_308518_zh-cn]: http://support.microsoft.com/kb/308518/zh-cn
[http_topic.csdn.net_t_20040109_16_2650137.html]: http://topic.csdn.net/t/20040109/16/2650137.html
[http_topic.csdn.net_u_20080526_16_f6e18b4d-876a-4ac1-bf1f-c359a6786d3b.html]: http://topic.csdn.net/u/20080526/16/f6e18b4d-876a-4ac1-bf1f-c359a6786d3b.html
[count]: mailto:=@count
[id_i]: mailto:id=@i
[spid]: mailto:'%20rel=
[http_topic.csdn.net_t_20030922_10_2285767.html]: http://topic.csdn.net/t/20030922/10/2285767.html
[http_www.cnblogs.com_LCX_archive_2008_12_03_1346924.html]: http://www.cnblogs.com/LCX/archive/2008/12/03/1346924.html