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.