locked
Sql command to check if a stored procedure is running? RRS feed

  • Question

  • User-1410783915 posted

    Using a Sql query is it possible to tell if a sql stored procedure is running? 

    Monday, June 13, 2016 5:10 AM

Answers

All replies

  • User632428103 posted

    Hello gleeming,

    never try but there are some article on google : 

    http://www.sqlservercentral.com/articles/DMV/64425/

    http://blog.sqlauthority.com/2009/01/07/sql-server-find-currently-running-query-t-sql/

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 13, 2016 5:47 AM
  • User1559292362 posted

    Hi Gleeming,

    Using a Sql query is it possible to tell if a sql stored procedure is running? 

    Is the real purpose that you want to avoid multiple instances of the procedure running? if it is the case, please use a session level application lock:

    CREATE PROCEDURE your_sp AS
    
    DECLARE @ret int,
            @lockresource sysname
    
    SELECT @lockresource = object_name(@@procid)
    
    BEGIN TRY
       EXEC @ret = sp_getapplock @lockresource, @LockMode = 'Exclusive',
                                 @LockOwner = 'Session', @LockTimeout = 5
    
       IF @ret <> 0
       BEGIN
          RAISERROR ('Another instance of the procedure is already running, 16, 1)
          RETURN
       END
    
       -- Do stuff
    
       EXEC sp_releaseapplock @lockresource, 'Session'
    END TRY
    BEGIN CATCH
       IF @@trancount > 0 ROLLBACK TRANSACTIOn
       EXEC sp_releaseapplock @lockresource, 'Session'
    END CATCH   

    Best regards,

    Cole Wu

    Tuesday, June 14, 2016 5:05 AM