Sep 062014

Here is a sweet little script that can check which stored procedures are running and perform an action.  In this case I need to update a configuration table if the stored procedure is not running.

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

DECLARE @spName 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 @table TABLE ( EventType nvarchar(30) , [Parameters] int , EventInfo nvarchar(4000) )   -- the table variable holding the result of DBCC INPUTBUFFER execution

DECLARE @tblProcs as TABLE (i int identity, spName nvarchar(100),IsRunning BIT) 
DECLARE @i int
SET @i = 1

INSERT INTO @tblProcs (spName, IsRunning) SELECT Name, 0 as IsRunning FROM dbo.sysobjects WHERE (type = 'P')

    DECLARE procs CURSOR FOR SELECT session_id FROM sys.dm_exec_requests WHERE status IN ('running', 'suspended', 'pending', 'runnable') AND session_id <> @@SPID ORDER BY session_id DESC  -- these are the processes to examine

    OPEN procs
    FETCH NEXT FROM procs INTO @handle

WHILE @i <= (Select count(*) from @tblProcs)

SELECT @spName = spName FROM @tblProcs WHERE i=@i

        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
            SET @sql = ''
        END CATCH
        IF CHARINDEX( @spName, @sql, 0 ) > 0
            UPDATE @tblProcs SET IsRunning = IsRunning + 1 WHERE spName = @spName

SET @i = @i + 1

        FETCH NEXT FROM procs INTO @handle
    CLOSE procs DEALLOCATE procs

UPDATE [admin].[ConfigurationsApp] SET [Value] = 'NOT RUNNING' 
FROM [admin].[ConfigurationsApp]
INNER JOIN @tblProcs as p on P.spName = [admin].[ConfigurationsApp].Name
WHERE IsRunning = 0


Thanks to John Billiris who provided a good portion of this solution in this post

  One Response to “Check if Stored Procedure is Running”

  1. Thanks! Worked like a charm.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>



Wordpress SEO Plugin by SEOPressor