clickstefan clickstefan - 2 months ago 18
SQL Question

Check if stored procedure is running

How to check if a stored procedure or query is still running in SQL Server?

Ideas


  1. I've thought of having a log where to write when the procedure starts and delete when it ends.

    Flaws:


    • it leaves open the case when the server restarts or some kind of failure inside the procedure.

    • this method needs some work to be done before running the procedure, so it can't be applied on already running procedures.


  2. Use process monitor



I would prefer a solution that can be incorporated as a stored procedure with
procedure_name
and/or
pid
,
parameters
as input, so tracing programs or solutions using the SQL Server interface won't work.




Update #1






Usage example:

CREATE PROCEDURE dbo.sp_sleeping_beauty
@time_str varchar(50)
AS
SET NOCOUNT ON;
WAITFOR DELAY @time_str;
GO

dbo.sp_sleeping_beauty '00:00:10'
dbo.sp_sleeping_beauty '00:00:20'
dbo.sp_sleeping_beauty '00:00:30'


the procedure should be called like

test_if_running 'dbo.sp_sleeping_beauty '00:00:20''


and return true while running (for 20 seconds) and false after or if the function fails or the system is restarted

Answer

You might query sys.dm_exec_requests which will provide sesion_ID, waittime and futher rows of interest and CROSS APPLY sys.dm_exec_sql_text filtering your query with the SQL for your procedure.

Select * from
(
SELECT * FROM sys.dm_exec_requests 
where sql_handle is not null
) a 
CROSS APPLY  sys.dm_exec_sql_text(a.sql_handle) t 
where t.text like 'CREATE PROCEDURE dbo.sp_sleeping_beauty%'