CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Stefan Rogin
Stefan Rogin

Reputation: 1527

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

Upvotes: 21

Views: 122590

Answers (5)

Design.Garden
Design.Garden

Reputation: 4247

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.

Upvotes: 23

HMan06
HMan06

Reputation: 785

Old thread but you can do this,

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

Upvotes: 4

John Clayton
John Clayton

Reputation: 11

I have been trying to figure out how to do get the list of running procedures and came across this thread. After a bit of research on MSDN I was able to figure out the following query that will provide the 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

A little bit more work will be needed to get the parameters.

Upvotes: 1

bummi
bummi

Reputation: 27384

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

Upvotes: 14

Navneet
Navneet

Reputation: 447

Use this :

exec sp_who2

It returns all db activities.

you will check from this proc if your procedure currently running or not.

Also will try that : 

 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;

Upvotes: 4

Related Questions