SQL Server进程阻塞的检查和解决办法.doc

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 ERROR0 RETURN ERROR insert into #tmp_lock_who(spid,bl) select 0 ,blocked from (select * from sysprocesses where blocked0 ) a where not exists(select * from (select * from sysprocesses where blocked0 ) b where a.blocked=spid) union select spid,blocked from sysprocesses where blocked0 IF ERROR0 RETURN ERROR - 找到临时表的记录数 select intCountProperties = Count(*),intCounter = 1 from #tmp_lock_who IF ERROR0 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 -结束SQL阻塞的进程%create procedure sp_Kill_lockProcess as begin SET NOCOUNT ON declare spid int,bl int, intTransactionCountOnEntry int, intRowcount int, intCountProperties int, intCounter int, sSql nvarchar (200) create table #tmp_lock_who ( id int identity(1,1), spid smallint, bl smallint) IF ERROR0 RETURN ERROR insert into #tmp_lock_who(spid,bl) select 0 ,blocked from (select * from sysprocesses where blocked0 ) a where not exists ( select * from (select * from sysprocesses where blocked0 ) b where a.blocked=spid ) union select spid,blocked from sysprocesses where blocked0 IF ERROR0 RETURN ERROR - 找到临时表的记录数 select intCountProperties = Count(*),intCounter = 1 from #tmp_lock_who IF ERROR0 RETURN ERROR while intCounter = intCountProperties begin - 取第一条记录 select spid = spid,bl = bl from #tmp_lock_who where Id = intCounter begin if spid =0 begin set sSql=kill + CAST(bl AS VARCHAR(10) exec sp_executesql sSql end end - 循环指针下移 set intCounter = intCounter + 1 end drop table #tmp_lock_who SET NOCOUNT OFF return 0 end GO查看锁信息 如何查看系统中所有锁的详细信息?在企业管理管理器中，我们可以看到一些进程和锁的信息，这里介绍另外一种方法。 -查看锁信息 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,Nobjname 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 R