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

《SQL Server进程阻塞的检查和解决办法》由会员分享，可在线阅读，更多相关《SQL Server进程阻塞的检查和解决办法（3页珍藏版）》请在装配图网上搜索。

1、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 ,blo

2、cked 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 blocked0IF ERROR0 RETURN ERROR- 找到临时表的记录数select intCountProperties = Count(*),intCounter = 1 f

3、rom #tmp_lock_whoIF 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 beginif spid =0select 引起数据库阻塞的是:+ CAST(bl AS VARCHAR(IO) + 进程号，其执行的SQL 语法如下elseselec

4、t 进程号 SPID：+ CAST(spid AS VARCHAR(10)+ 被 + 进程号 SPID：+ CAST(bl AS VARCHAR(IO) +阻塞，其当前进程执行的SQL语法如下DBCC INPUTBUFFER (bl ) end- 循环指针下移set intCounter = intCounter + 1end drop table #tmp_lock_whoreturn 0endGO-结束 SQL 阻塞的进程% create procedure sp_Kill_lockProcessasbeginSET NOCOUNT ONdeclare spid int,bl int,in

5、tTransactionCountOnEntry 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 ERRORinsert into #tmp_lock_who(spid,bl)select 0 ,blockedfrom(select * from sysprocesses where blocked0 ) a

6、where not exists(select * from (select * from sysprocesses where blocked0 ) bwhere a.blocked=spid)union select spid,blocked from sysprocesses where blocked0IF ERROR0 RETURN ERROR- 找到临时表的记录数select intCountProperties = Count(*),intCounter = 1from #tmp_lock_whoIF ERROR0 RETURN ERRORwhile intCounter = i

7、ntCountPropertiesbegin- 取第一条记录select spid = spid,bl = blfrom #tmp_lock_who where Id = intCounterbeginif spid =0beginset sSql=kill + CAST(bl AS VARCHAR(10)exec sp_executesql sSqlendend- 循环指针下移set intCounter = intCounter + 1enddrop table #tmp_lock_whoSET NOCOUNT OFFreturn 0endGO 查看锁信息如何查看系统中所有锁的详细信息?在

8、企业管理管理器中，我们可以看到一些进程和锁 的信息，这里介绍另外一种方法。-查看锁信息create table #t(req_spid int,obj_name sysname)declare s nvarchar(4000),rid int,dbname sysname,id int,objname sysname declare tb cursor forselect distinct req_spid,dbname=db_name(rsc_dbid),rsc_objidfrom master.syslockinfo where rsc_type in(4,5)open tbfetch n

9、ext from tb into rid,dbname,idwhile fetch_status=0beginset s=select objname=name from +dbname+.sysobjects whereid=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,idendclose tb deallocate tb select 进程 id=a.req_spi

10、d，数据库=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_indidfrom master.syslockinfo a left join #t b on a.req_spid=b.req_spid godrop table #t