SQL2005查看死锁存储过程sp_who_lock
下面是我整理的监控sqlserver数据库，在性能测试过程中是否出现死锁、堵塞的SQL语句，还算比较准备，留下来备用。
调用方法：选中相应的数据库，执行execsp_who_lock
USE[master] GO SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO CREATEprocedure[dbo].[sp_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_lock_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@intCountProperties=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@spid=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 return0 end