How to determine if a SQL stored procedure is running : Part I
Continuing from my previous post on How to determine if a SQL job is running, I was asked to determine if a certain stored procedure was currently running.
Well, here it gets quite tricky as this has to be solved
based on the answer to the following question :-
Question: If stored procedure "B" is currently running from within stored procedure "A", what is the expected answer? Is it "A" or "B" ?
In this part, I will tackle the answer to the "A" scenario.
In case you didn't know, SSMS Activity Monitor provides the ability to view the currently executing SQL for your chosen process. And it happens to be the answer we're looking for. Activity Monitor performs this using a classic DBCC command
Unfortunately, DBCC commands can't be used within queries, but can have their results inserted into temporary tables or table variables.
With this in mind, here we go...
The task at hand involves retrieving the current processes
(in certain states, see below) and checking DBCC INPUTBUFFER
for the originating SQL for that process (be it a stored
procedure, SQL from (say) a Query window in SSMS).
DECLARE @stringBeingSought NVARCHAR(MAX) -- the string
we're looking for. The variable to become a parameter if
you wish to extend this sp
DECLARE @handle
SMALLINT -- the spid of the process
DECLARE @sql
NVARCHAR(MAX) -- the dynamic SQL
DECLARE @isRunning
BIT -- the result
DECLARE @table TABLE ( EventType
nvarchar(30) , [Parameters] int , EventInfo nvarchar(4000)
) -- the table variable holding the result of DBCC
INPUTBUFFER execution
SET @isRunning = 0
SET @stringBeingSought =
'sp_ALongRunningProcedure'
DECLARE procs CURSOR
FOR SELECT session_id FROM sys.dm_exec_requests WHERE
status IN ('running', 'suspended', 'pending', 'runnable')
ORDER BY session_id DESC -- these are the processes to
examine
OPEN procs
FETCH NEXT FROM procs INTO @handle
WHILE
@@FETCH_STATUS=0
BEGIN
BEGIN TRY
DELETE FROM @table
SET @sql = 'DBCC INPUTBUFFER(' + CAST(@handle AS
NVARCHAR) + ')'
INSERT
INTO @table
EXEC (@sql)
SELECT @sql = EventInfo FROM @table
END TRY
BEGIN CATCH
SET @sql = ''
END
CATCH
IF CHARINDEX(
@stringBeingSought, @sql, 0 ) > 0
BEGIN
SET @isRunning = 1
END
FETCH NEXT
FROM procs INTO @handle
END
CLOSE procs
DEALLOCATE procs
SELECT ISNULL(@isRunning, 0)
And there you go...
In Part II, we'll tackle the "B" scenario.