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

createproceduresp_who_lockasbegindeclare@spidint,@blint,@intTransactionCountOnEntryint,@intRowcountint,@intCountPropertiesint,@intCounterintcreatetable#tmp_lock_who(idintidentity(1,1),spidsmallint,blsmallint)IF@@ERROR<>0RETURN@@ERRORinsertinto#tmp_lock_who(spid,bl)select0,blockedfrom(select*fromsysprocesseswhereblocked>0)awherenotexists(select*from(select*fromsysprocesseswhereblocked>0)bwherea.blocked=spid)unionselectspid,blockedfromsysprocesseswhereblocked>0IF@@ERROR<>0RETURN@@ERROR--找到临时表的记录数select@intCountProperties=Count(*),@intCounter=1from#tmp_lock_whoIF@@ERROR<>0RETURN@@ERRORif@intCountProperties=0select'现在没有阻塞信息'asmessage--循环开始while@intCounter<=@intCountPropertiesbegin--取第一条记录select@spid=spid,@bl=blfrom#tmp_lock_whowhereId=@intCounterbeginif@spid=0select'引起数据库阻塞的是:'+CAST(@blASVARCHAR(IO))+'进程号，其执行的SQL语法如下'elseselect'进程号SPID：'+CAST(@spidASVARCHAR(10))+'被'+'进程号SPID：'+CAST(@blASVARCHAR(IO))+'阻塞，其当前进程执行的SQL语法如下’DBCCINPUTBUFFER(@bl)end--循环指针下移set@intCounter=@intCounter+1enddroptable#tmp_lock_whoreturn0endGO--结束SQL阻塞的进程%%%%%%%%%%%%%%%%%%%%%%createproceduresp_Kill_lockProcessasbeginSETNOCOUNTONdeclare@spidint,@blint,@intTransactionCountOnEntryint,@intRowcountint,@intCountPropertiesint,@intCounterint,@sSqlnvarchar(200)createtable#tmp_lock_who(idintidentity(1,1),spidsmallint,blsmallint)IF@@ERROR<>0RETURN@@ERRORinsertinto#tmp_lock_who(spid,bl)select0,blockedfrom(select*fromsysprocesseswhereblocked>0)awherenotexists(select*from(select*fromsysprocesseswhereblocked>0)bwherea.blocked=spid)unionselectspid,blockedfromsysprocesseswhereblocked>0IF@@ERROR<>0RETURN@@ERROR--找到临时表的记录数select@intCountProperties=Count(*),@intCounter=1from#tmp_lock_whoIF@@ERROR<>0RETURN@@ERRORwhile@intCounter<=@intCountPropertiesbegin--取第一条记录select@spid=spid,@bl=blfrom#tmp_lock_whowhereId=@intCounterbeginif@spid=0beginset@sSql='kill'+CAST(@blASVARCHAR(10))execsp_executesql@sSqlendend--循环指针下移set@intCounter=@intCounter+1enddroptable#tmp_lock_whoSETNOCOUNTOFFreturn0endGO查看锁信息如何查看系统中所有锁的详细信息?在企业管理管理器中，我们可以看到一些进程和锁的信息，这里介绍另外一种方法。--查看锁信息createtable#t(req_spidint,obj_namesysname)declare@snvarchar(4000),@ridint,@dbnamesysname,@idint,@objnamesysnamedeclaretbcursorforselectdistinctreq_spid,dbname=db_name(rsc_dbid),rsc_objidfrommaster..syslockinfowherersc_typein(4,5)opentbfetchnextfromtbinto@rid,@dbname,@idwhile@@fetch_status=0beginset@s='select@objname=namefrom['+@dbname+']..sysobjectswhereid=@id'execsp_executesql@s,N'@objnamesysnameout,@idint',@objnameout,@idinsertinto#tvalues(@rid,@objname)fetchnextfromtbinto@rid,@dbname,@idendclosetbdeallocatetbselect进程id=a.req_spid，数据库=db_name(rsc_dbid)，类型=casersc_typewhen1then'NULL资源(未使用)'when2then'数据库'when3then'文件'when4then'索引'when5then'表'when6then'页'when7then'键'when8then'扩展盘区'when9then'RID(/r