查看死結的SQL語句

use master go declare @spid int,@bl int DECLARE s_cur CURSOR FOR select  0 ,blocked from (select * from sysprocesses where  blocked>0 ) a where not exists(select * from (select * from sysprocesses where  blocked>0 ) b where a.blocked=spid) union select spid,blocked from sysprocesses where  blocked>0 OPEN s_cur FETCH NEXT FROM s_cur INTO @spid,@bl WHILE @@FETCH_STATUS = 0 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 ) FETCH NEXT FROM s_cur INTO @spid,@bl end CLOSE s_cur DEALLOCATE s_cur  exec sp_who2 

運行sp_who2 ,看blk by 欄， 如果這欄不是空白， 有一個數字， 這就是造成阻塞（blocking）的線程id. 然後運行DBCC Inputbuffer(線程id), 你就可以看見這個線程在幹什麼。

來源：http://club.techtarget.com.cn/showtopic-42012-1.aspx