Tag: check

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.

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

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 @@FETCH_STATUS=0 
    BEGIN

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

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

SET @i = @i + 1
END

        FETCH NEXT FROM procs INTO @handle
    END
    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

END

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