Check if the stored procedure is running

Question

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

Ideas

• I thought about having a journal where to write when a procedure is started and deleted when it ends.

  Disadvantages:

  • it leaves open the case when the server reboots or some kind of failure inside the procedure.
  • This method requires some work that must be done before the procedure starts, so it cannot be applied to already running procedures.

• Use a process monitor

I would prefer a solution that can be included as a stored procedure with procedure_name and / or pid , parameters as input, so tracing programs or solutions using the SQL Server interface will not 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 as

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

and return true during operation (within 20 seconds) and false after or if the function fails or system reboots

Answer 1

You can query sys.dm_exec_requests , which will provide sesion_ID, waittime and more interesting strings, and CROSS APPLY sys.dm_exec_sql_text , filtering your query with 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%' 

Answer 2

Use this:

 exec sp_who2 

It returns all db actions.

You will check this process if your procedure is currently running or not.

Also try:

  SELECT creation_time , object_name(st.objectid) as ProcName ,last_execution_time ,total_physical_reads ,total_logical_reads ,total_logical_writes , execution_count , total_worker_time , total_elapsed_time , total_elapsed_time / execution_count avg_elapsed_time ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY total_elapsed_time / execution_count DESC; 

Answer 3

Old thread, but you can do it,

 SELECT @object = object_id FROM SYS.OBJECTS WHERE NAME = [SP NAME] 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 objectid = @object 

Answer 4

I tried to figure out how to get a list of running procedures and came across this thread. After a little research on MSDN, I was able to figure out the following query, which will provide a list of running processes:

 select object_name(st.objectid) as ProcName from sys.sysprocesses as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st where object_name(st.objectid) is not null 

Getting the parameters will require a bit more work.