Check If Stored Procedure Is Running

Check if stored procedure is running

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

Check if a stored proc is running?

I asked this once :)

Check out:

Sql Server 2000 - How can I find out what stored procedures are running currently?

Find out if the stored procedure is already running

Use the built-in application locks. SQL Server makes sure the lock is released if the session or server shuts down unexpectedly. Be aware that the lock must be acquired inside a transaction.

ALTER PROCEDURE usp_ProcessCustomers
AS
BEGIN
    BEGIN TRANSACTION
    
    declare @Lock int
    EXEC @Lock = sp_getapplock @Resource = 'ProcessCustomers',
                               @LockMode = 'Exclusive'
    IF (@Lock < 0)  -- already locked by another process
       RETURN;

-- Do processing here
-- Do processing here

    EXEC sp_releaseapplock @Resource = 'ProcessCustomers'
    
    COMMIT TRANSACTION
END

How to check if the stored procedure is scheduled to run at specific time

It's too old question but still I am posting this. You can try -

CREATE PROCEDURE MyTask
 AS
BEGIN  
    SET NOCOUNT ON;
    --  For executing the stored procedure at 11:00 P.M
    declare @delayTime nvarchar(50)
    set @delayTime = '23:00'

    while 1 = 1
    begin
        waitfor time @delayTime 
        begin
            --Name for the stored proceduce you want to call on regular bases
            execute [DatabaseName].[dbo].[StoredProcedureName];
        end
    end
END

Then,

-- Sets stored procedure for automatic execution.
sp_procoption    @ProcName = 'MyTask',
                @OptionName = 'startup',
                @OptionValue = 'on'

Check If JavaScript Procedure is Already Running

Yes, you can use the EXECUTION_STATUS column of QUERY_HISTORY to determine whether a stored procedure call is still running or not.

However, in this case, the ACCOUNT_USAGE.QUERY_HISTORY view cannot be used because this view has some delay. In other words, it cannot capture the real-time execution status. So, the INFORMATION_SCHEMA.QUERY_HISTORY table function is preferred.

Below is an example of how to use the INFORMATION_SCHEMA.QUERY_HISTORY table function in this use case. Please note that INFORMATION_SCHEMA.QUERY_HISTORY is a table function, not a view, so you have to use the TABLE() wrapper to use it as below:

Worksheet 1:

-- Sample stored procedure just waiting for 60 seconds

create or replace procedure sp1 ()
returns varchar
language javascript
as 'snowflake.createStatement({sqlText: "select system$wait(60);"}).execute();'
;

call sp1();

Worksheet 2:

select start_time, query_id, query_text, execution_status
from table(information_schema.query_history())
where execution_status = 'RUNNING'
;
/*
START_TIME  QUERY_ID    QUERY_TEXT  EXECUTION_STATUS
2021-10-26 08:25:56.969 +0200   019fdc41-0000-2c1d-0000-3f8100091e56    select start_time, query_id, query_text, execution_status  from table(information_schema.query_history())  where execution_status = 'RUNNING'  ;    RUNNING
2021-10-26 08:25:53.869 +0200   019fdc41-0000-2c5c-0000-3f81000935aa    select system$wait(60); RUNNING
2021-10-26 08:25:53.515 +0200   019fdc41-0000-2c5c-0000-3f81000935a6    call sp1(); RUNNING
*/

Please note that the above result from the QUERY_HISTORY table function with EXECUTION_STATUS = 'RUNNING' includes the QUERY_HISTORY query itself. So, if you only collect the EXECUTION_STATUS column as your example, it's difficult to distinguish whether the running query is the stored procedure call or not.

Therefore, if a human uses the status check query to check visually, the query should include other columns like QUERY_ID, START_TIME and QUERY_TEXT to distinguish the stored procedure call.

Otherwise, if any automation uses the status check query, the query should have another filter (WHERE clause) to distinguish the stored procedure call as below:

select query_id
from table(information_schema.query_history())
where execution_status = 'RUNNING'
and query_text ilike 'call%'
;
/*
QUERY_ID
019fdc48-0000-2c1d-0000-3f8100091e6e
*/

You can change the pattern in the new filter to distinguish different stored procedure calls, and also you can use a query tag:

Worksheet 1:

alter session set query_tag = 'ws1';
call sp1();

Worksheet 2:

alter session set query_tag = 'ws2';
call sp1();

Worksheet 3:

select query_tag, query_id
from table(information_schema.query_history())
where execution_status = 'RUNNING'
and query_text ilike 'call%'
;
/*
QUERY_TAG   QUERY_ID
ws2 019fdc4b-0000-2c5c-0000-3f8100093642
ws1 019fdc4b-0000-2c1d-0000-3f8100091ec6
*/

select query_tag, query_id
from table(information_schema.query_history())
where execution_status = 'RUNNING'
and query_text ilike 'call%'
and query_tag = 'ws2'
;
/*
QUERY_TAG   QUERY_ID
ws2 019fdc4b-0000-2c5c-0000-3f8100093642
*/

How to check if a proc is already running when called?

Given that the desire is to have any process calling sp_Task2 wait until sp_Task2 completes if it is already running, that is essentially making sp_Task2 single-threaded.

This can be accomplished through the use of Application Locks (see sp_getapplock and sp_releaseapplock). Application Locks let you create locks around arbitrary concepts. Meaning, you can define the @Resource as "Task2" which will force each caller to wait their turn. It would follow this structure:

    BEGIN TRANSACTION;
    EXEC sp_getapplock @Resource = 'Task2', @LockMode = 'Exclusive';

    ...single-threaded code...

    EXEC sp_releaseapplock @Resource = 'Task2';
    COMMIT TRANSACTION;

You need to manage errors / ROLLBACK yourself (as stated in the linked MSDN documentation) so put in the usual TRY / CATCH. But, this does allow you to manage the situation.

This code can be placed either in sp_Task2 at the beginning and end, as follows:

CREATE PROCEDURE dbo.Task2
AS
SET NOCOUNT ON;

BEGIN TRANSACTION;
EXEC sp_getapplock @Resource = 'Task2', @LockMode = 'Exclusive';

{current logic for Task2 proc}

EXEC sp_releaseapplock @Resource = 'Task2';
COMMIT TRANSACTION;

Or it can be placed in all of the locations that calls sp_Task2, as follows:

CREATE PROCEDURE dbo.Task1
AS
SET NOCOUNT ON;

BEGIN TRANSACTION;
EXEC sp_getapplock @Resource = 'Task2', @LockMode = 'Exclusive';

EXEC dbo.Task2 (with calling parameters if any);

EXEC sp_releaseapplock @Resource = 'Task2';
COMMIT TRANSACTION;

I would think that the first choice -- placing the logic in sp_Task2 -- would be the cleanest since a) it is in a single location and b) cannot be avoided by someone else calling sp_Task2 outside of the currently defined paths (ad hoc query or a new proc that doesn't take this precaution).

Please see my answer to your initial question regarding not using the sp_ prefix for stored procedure names and not needing the return value.

Please note: sp_getapplock / sp_releaseapplock should be used sparingly; Application Locks can definitely be very handy (such as in cases like this one) but they should only be used when absolutely necessary.

How can I know which stored procedure is running in SQL Server 2005

SQL Profiler is usually a good start to log activity on a SQL Server instance

How to check if a stored procedure exists before creating it

You can run procedural code anywhere you are able to run a query.

Just copy everything after AS:

BEGIN
    DECLARE @myvar INT
    SELECT  *
    FROM    mytable
    WHERE   @myvar ...
END

This code does exactly same things a stored proc would do, but is not stored on the database side.

That's much like what is called anonymous procedure in PL/SQL.

Update:

Your question title is a little bit confusing.

If you only need to create a procedure if it not exists, then your code is just fine.

Here's what SSMS outputs in the create script:

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'myproc')
                    AND type IN ( N'P', N'PC' ) ) 
DROP …
CREATE …

Update:

Example of how to do it when including the schema:

IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'[dbo].[MyProc]') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE [dbo].[MyProc]
END

In the example above, dbo is the schema.

Update:

In SQL Server 2016+, you can just do

CREATE OR ALTER PROCEDURE dbo.MyProc

---

Related Topics