Find out if the stored procedure is already running

Question

Here is my stored procedure which I am running in SQL Server 2012. ``` ALTER PROCEDURE usp_ProcessCustomers AS BEGIN IF EXISTS (SELECT 1 FROM RunningProcesses WHERE ProcessId = 1 AND IsRunning = 1) RETURN; UPDATE RunningProcesses SET IsRunning = 1 WHERE ProcessId = 1 -- Do processing here -- Do processing here UPDATE RunningProcesses SET IsRunning = 0 WHERE ProcessId = 1 END GO ``` This stored procedure can be triggered from several places in the app. Even the DBA can trigger the stored procedure using SSMS if needed. So far so good. The issue is that if something goes wrong or if the DBA cancels the execution of the stored procedure then the `IsRunning` value in `RunningProcesses` is never updated back to 0. So the system always thinks that the stored procedure is running even when its not. I found the following script on the web which checks if a script is running. ``` SELECT r.*, t.text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.status IN (…

Accepted Answer

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
```

Find out if the stored procedure is already running

Solution

Problem