How to check if a Stored Procedure is running?

Question

How to check if a Stored Procedure is running?

JanM-66

Ten Centuries

Points: 1247
More actions
December 11, 2007 at 8:59 am

#121321

Hi
We have an SP who can be started by users and should not run twice at the same time.
Could not find an object_id entry in sysprocesses to verify if a Stored Procedure is running.
Where can be checked of the SP is currently running?
Thanks for help.
Jan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Rather than checking if something is running, you should lock a resource. If your procedure can take an exclusive lock on a table or row, it will automatically wait for another instance. If there are no objects used by the procedure that you can do this with appropriately, see these two stored procedures:

sp_getapplock

sp_releaseapplock

JanM-66 Ten Centuries Points: 1247 More actions · Answer 2

Because of external applications we can't use transactions, what seems to be used for sp_getapplock.

Can a global var be set?

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

If it's that critical, create a "token table" where you set a row to identify if the job is already running. Check for that token at the beginning of the run. Has the side benefit of identifying when the job was run and, with a bit of fore thought, can be used to indeify how the job took and, possibly, how long the "steps" in the job took.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

If you don't care about actual tracking, you can still use a "blocking table" with a unique index on it (i.e. something allowing only one record in the particular table).

First thing in your SP - try to insert a record into this table. If it works, do the rest of the SP, otherwise, come back later

something like...

--your blocking table - generate only once

create table blocker(appname varchar(50) primary key)

--use TRY/Catch

create procedure singlethread

as

begin

declare @appname varchar(50)

set @appname='MyApplication'

Begin Try

insert blocker select @appname --since appname is Primary Key - it can only be insert ONE TIME

--go on and do whatever

--still doing stuff

--etc....

end try

begin catch

select 'singlethread is currently running - try back later',error_number(),error_message()

end catch

delete from blocker where appname=@appname

end

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

I am not going to post the details of sp_getapplock - they are in books online. You specify a name for what you are locking and if another spid tried to lock the same name it will wait.

It is pretty much the same as using a table to indicate your procedure is already running with two very important advantages.

First, if your procedure fails for some reason (connection lost is a good example) before it gets to the place in which it indicates in the blocking table that it is no longer running, it will never actually do this. The only remedy I can think of right now in a blocking table is to have your procedure in a transaction to roll back the insert into the blocking table if there is a problem. With sp_getapplock your lock can only exist as long as the calling spid is still alive so the clean-up is automatic.

The second advantage is in waiting. When you call sp_getapplock, you can specify a lock timeout. So, if you happen to run the procedure near the end of it currently running, the second execution of the procedure can wait for the first one to complete rather than giving an immediate error. Since you can control this amount of time, this is pretty useful in dealing with minor timing issues. If the procedure is pretty fast but just causes a conflict, useing the wait feature will queue up multiple requests and process them in the order they were received.

Miron Berlin SSC-Addicted Points: 414 More actions · Answer 6

JanM (12/11/2007)
we can't use transactions, what seems to be used for sp_getapplock.

not nesessarilly, it uses xp_userlock internally, and with @LockOwner set to 'Session' it will only honor current connection. Just read on - line documentation.