How to find which query is currently running in SQL Server 2000.

The below given function will return the currently running query for the process id.

STEP1: Create the below function in your server database.

CREATE FUNCTION SHOW_MY_PROCESS (@SPID INT)
RETURNS VARCHAR(8000)AS
BEGIN   
    DECLARE @sql_handle BINARY(20), @handle_found BIT   
    DECLARE @stmt_start INT, @stmt_end INT   
    DECLARE @line NVARCHAR(4000), @wait_str VARCHAR(8)   
    DECLARE @sql_process AS VARCHAR(8000)   
    SELECT  @sql_handle = sql_handle, @stmt_start = stmt_start/2,
    @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END   
    FROM master.dbo.SYSPROCESSES   
    WHERE spid = @spid AND ecid = 0
    SELECT @sql_process = 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)
    RETURN @sql_process
END

STEP2: Run the below query

SELECT dbo.SHOW_MY_PROCESS(SPID) FROM MASTER.DBO.SYSPROCESSES WHERE SPID > 50

0 comments:

Post a Comment