资源描述：
《sql server进程阻塞检查和解决办法》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、createproceduresp_who_lock as begin declare@spidint,@blint, @intTransactionCountOnEntryint, @intRowcountint, @intCountPropertiesint, @intCounterint createtable#tmp_lock_who( idintidentity(1,1), spidsmallint, blsmallint) IF@@ERROR<>0RETURN@@ERROR insertinto#tmp_l
2、ock_who(spid,bl)select0,blocked from(select*fromsysprocesseswhereblocked>0)a wherenotexists(select*from(select*fromsysprocesseswhereblocked>0)b wherea.blocked=spid) unionselectspid,blockedfromsysprocesseswhereblocked>0 IF@@ERROR<>0RETURN@@ERROR --找到临时表的记录数 select@intC
3、ountProperties=Count(*),@intCounter=1 from#tmp_lock_who IF@@ERROR<>0RETURN@@ERROR if@intCountProperties=0 select'现在没有阻塞信息'asmessage --循环开始 while@intCounter<=@intCountProperties begin --取第一条记录 select@spid=spid,@bl=bl from#tmp_lock_whowhereId=@intCounter begin if@s
4、pid=0 select'引起数据库阻塞的是:'+CAST(@blASVARCHAR(10))+'进程号,其执行的SQL语法如下' else select'进程号SPID：'+CAST(@spidASVARCHAR(10))+'被'+'进程号SPID：'+CAST(@blASVARCHAR(10))+'阻塞,其当前进程执行的SQL语法如下' DBCCINPUTBUFFER(@bl) end --循环指针下移 set@intCounter=@intCounter+1 end droptable#tmp_lock_who ret
5、urn0 end GO --结束SQL阻塞的进程%%%%%%%%%%%%%%%%%%%%%%create proceduresp_Kill_lockProcess as begin SETNOCOUNTON declare@spidint,@blint, @intTransactionCountOnEntryint, @intRowcountint, @intCountPropertiesint, @intCounterint, @sSqlnvarchar(200) createtable#tmp_lock_who
6、( idintidentity(1,1), spidsmallint, blsmallint) IF@@ERROR<>0RETURN@@ERROR insertinto#tmp_lock_who(spid,bl) select0,blocked from (select*fromsysprocesseswhereblocked>0)a wherenotexists ( select*from(select*fromsysprocesseswhereblocked>0)b wherea.blocked=spid ) u
7、nionselectspid,blockedfromsysprocesseswhereblocked>0 IF@@ERROR<>0RETURN@@ERROR --找到临时表的记录数 select@intCountProperties=Count(*),@intCounter=1 from#tmp_lock_who IF@@ERROR<>0RETURN@@ERROR while@intCounter<=@intCountProperties begin --取第一条记录 select@spid=spid,@bl=bl from
8、#tmp_lock_whowhereId=@intCounter begin if@spid=0 begin set@sSql='kill'+
