[X]

How to get the query running under a process in SQL Server

SQL Server Queries, Running under a process

Some time it requires to know what actually the SQL Server is executing under a process. So that we can know where exactly the procedure/query is stuck right now. Which helps us to know whether we can kill the running process or not. By making use of the inputbuffer it gives only the procedure (with parameteres) currently executed by the process, in the case of a procedure. Below query gives you the excat query executed by SQL Server.

 

Run below script in sql server management studio query window:

 

DECLARE @spid INT, @stmt_start INT, @stmt_end INT, @sql_handle BINARY(20) 

SET @spid = XXX -- Fill this in
 
SELECT
	TOP 1
		@sql_handle = sql_handle,
		@stmt_start = CASE stmt_start WHEN 0 THEN 0 ELSE stmt_start / 2 END,
		@stmt_end = CASE stmt_end WHEN -1 THEN -1 ELSE stmt_end / 2 END
FROM
	master.dbo.sysprocesses
WHERE
	spid = @spid
ORDER BY ecid 

SELECT 
    SUBSTRING(  TEXT, 
                COALESCE(NULLIF(@stmt_start, 0), 1), 
                CASE @stmt_end 
                        WHEN -1 
                                THEN DATALENGTH(TEXT) 
                        ELSE 
                                (@stmt_end - @stmt_start) 
                        END 
        ) 
FROM ::fn_get_sql(@sql_handle)

 

 

blog comments powered by Disqus

Posts By Month