How to check if a stored procedure or query is still running in SQL Server?
Ideas
I've thought of having a log where to write when the procedure starts and delete when it ends.
Flaws:
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.
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
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%'
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments