找到 sql server 里面产生 block 的 session

;with ps

as

( select blocked as RootID, spid asChildID, 1 as LevelBlock

frommaster.sys.sysprocesses rt

wheredbid=db_id('intelligencesandbox') and rt.Blocked = 0

union all

select cl.blockedas RootID, cl.spid as ChildID, LevelBlock + 1 as LevelBlock

from ps

inner joinmaster.sys.sysprocesses cl on ps.ChildID = cl.Blocked andcl.dbid=db_id('intelligencesandbox')

--where cl.blockednot in ( select distinct RootID from ps )

--andcl.blocked<>0

)

select RootID asblockingID, ChildID as blockedID , LevelBlock from ps

--where levelblock <=2

order by level block

相比于这种手工看的方法，sql server 还提供了更完整的自带的工具，在后面的笔记中会有详细的记载：

http://blog.csdn.net/wujiandao/article/details/51039488