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
Thanks! Worked like a charm.