查询Blocking脚本

SELECT SPID=p.spid,

       DBName = convert(CHAR(20),d.name),

       ProgramName = program_name,

       LoginName = convert(CHAR(20),l.name),

       HostName = convert(CHAR(20),hostname),

       Status = p.status,

       BlockedBy = p.blocked,

       LoginTime = login_time,

       QUERY = CAST(TEXT AS VARCHAR(MAX))

FROM   MASTER.dbo.sysprocesses p

       INNER JOIN MASTER.dbo.sysdatabases d

         ON p.dbid = d.dbid

       INNER JOIN MASTER.dbo.syslogins l

         ON p.sid = l.sid

       CROSS APPLY sys.dm_exec_sql_text(sql_handle)

WHERE  p.blocked = 0

       AND EXISTS(SELECT 1

                   FROM   MASTER..sysprocesses p1

                   WHERE  p1.blocked = p.spid)