查看指定spid的脚本当前运行情况和状态

USE Master
GO

declare
	@spid int
;

select 
	@spid = 419--null:all 
;
 
;WITH DATA(spid,blockRelationship,blocked,spidLevel,hostname,program_name,loginame,login_time,BlockDuration,Status,sqlText,Memo,stmt_start,stmt_end,db_Name)
AS(
    SELECT spid
           ,CONVERT(VARCHAR(256),' ') AS blockRelationship
           ,blocked
           ,spidLevel = 1
           ,hostname
           ,program_name
           ,loginame
           ,A.login_time
           ,DATEDIFF(MINUTE,A.login_time,GETDATE()) AS BlockDuration
           ,A.Status
           ,B.text
           ,Memo = CONVERT (varchar(128), 'BlockRoot')
           ,A.stmt_start
           ,A.stmt_end
		   ,db_name(A.dbid) AS db_Name
    FROM sys.sysprocesses AS A WITH (NOLOCK) 
       CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) AS B
    WHERE Blocked = 0
    UNION ALL
    SELECT 
           A.spid
           ,CONVERT(varchar(128),REPLICATE('L' ,B.spidLevel)) + CONVERT (varchar(128), A.blocked) AS Sort
           ,A.blocked
           ,spidLevel+1
           ,A.hostname
           ,A.program_name
           ,A.loginame
           ,A.login_time
           ,DATEDIFF(MINUTE,A.login_time,GETDATE()) AS BlockDuration
           ,A.Status
           ,C.text
           ,Memo = 'Blocked by ' + CONVERT (varchar(117), A.blocked)
           ,A.stmt_start
           ,A.stmt_end
		   ,db_name(A.dbid) AS db_Name
    FROM sys.sysprocesses AS A WITH (NOLOCK)
       INNER JOIN  DATA AS B
        ON A.blocked = B.spid
       CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) AS C
    --WHERE B.blocked = 0
)
SELECT spid
       ,blockRelationship
       ,blocked
       ,login_time
	   ,GETDATE() AS [current_time]
		,sql_statement = (SELECT TOP 1 SUBSTRING(sqlText,stmt_start / 2+1 , 
                         ( 
                            (
                            CASE WHEN stmt_end = -1 THEN (LEN(CONVERT(nvarchar(max),sqlText)) * 2) 
                                   ELSE stmt_end END
                            )  - stmt_start) / 2+1
                         )
                      )
	   ,db_Name
       ,spidLevel
       ,hostname
       ,loginame
	   ,program_name
       --,login_time
       ,BlockDuration
       ,status
	   ,sqlText
       ,Memo
FROM DATA
--the block root spid
WHERE spidLevel = 1
    AND spID IN(
                  SELECT blocked
                  FROM DATA
              )
UNION ALL
SELECT spid
       ,blockRelationship
       ,blocked
       ,login_time
	   ,GETDATE()
		,sql_statement = (SELECT TOP 1 SUBSTRING(sqlText,stmt_start / 2+1 , 
                         ( 
                            (
                            CASE WHEN stmt_end = -1 THEN (LEN(CONVERT(nvarchar(max),sqlText)) * 2) 
                                   ELSE stmt_end END
                            )  - stmt_start) / 2+1
                         )
                      )
	   ,db_Name
       ,spidLevel
       ,hostname
       ,loginame
	   ,program_name
       --,login_time
       ,BlockDuration
       ,status
	   ,sqlText
       ,Memo
FROM DATA
WHERE spidLevel > 1

--kill 68

IF @spid is not null

	SELECT 
		database_name = DB_NAME(s1.dbid)
		,sql_statement = (SELECT TOP 1 SUBSTRING(s2.text,stmt_start / 2+1 , 
							 ( 
								(
								CASE WHEN stmt_end = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) 
									   ELSE stmt_end END
								)  - stmt_start) / 2+1
							 )
						  )
		,s2.text
		,Duration_min = DATEDIFF(MINUTE,s1.login_time,GETDATE())
		,s1.hostname
		,s1.status
		,s1.cpu
	FROM sys.sysprocesses AS s1 WITH(NOLOCK)
		CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 
	WHERE s1.spid = @spid;

--kill 87