clickstefan clickstefan - 1 year ago 107
SQL Question

Check if stored procedure is running

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


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


    • 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
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)
WAITFOR DELAY @time_str;

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 Source

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%'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download