Check if stored procedure is running

Question

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 1

Update: The answer given by John Clayton references the outdated SQL Server 2000 system table (sys.sysprocesses). The updated SQL is:

SELECT
  object_name(st.objectid) as ProcName
FROM
  sys.dm_exec_connections as qs 
CROSS APPLY sys.dm_exec_sql_text(qs.most_recent_sql_handle) st 
WHERE
  object_name(st.objectid) is not null

The SQL code above returns a list of names of your running processes. Note that you will need permission to view the Server/Database state.

Answer 2

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%'