How to detect a stored proc is running from TSQL?

I launch a stored proc from a job that is scheduled several times a day. I want the proc to detect if it is still running (from a previous process) and exit out if it is. I want to be absolutely sure it is or is not running.

Thanks!

I don't know of a straight forward way to check if a stored prcocedure is running but I have a couple of workarounds. Before that I just want to highlight if the only source that is running the stored procedure is the job you're talking about, then if it is still running and it is time for the next job, the next instance won't run so you wouldn't have the case that 2 job instances are running in the same time. Regardless here are the workarounds:

Include in your database a table that you can use to check the status of the stored procedure. For example a table called SystemStatus that includes a Key column and a Status column. Add a row for this procedure with initial value in the status false (for not running). Make the first statement in the procedure a check on the value of the status field for this row. If it true then another instance is running so end the procedure. If it is false then update it to true and let the final statement in the procedure update it back to false|||

I can't able to understand "I want the proc to detect if it is still running (from a previous process) and exit out if it is"

You mean, to find the current SP is already running on your Server (or job) before executing it.

If yes, you need not worry about it. Bcs if the current job is executing then the SQL server never initate the next scheduled job, it will wait to complete the current job, then it will find the next possible schedule to execute.

if you 2 or more parllel jobs with same SP (not really required) then you need to follow some log based events.

|||

Sami,

Thank you for your quick and detailed response. Actually I have used this method before. Unfortunately, this transaction is what I wanted to avoid here because it is a long-running proc that runs many other procs so I wanted to avoid holding blocking other users just to prevent my proc from running simultaneously. Thanks again!

Mr. P.

|||

You could use application locks in SQL Server. Something like below should work. This can be simplified a bit in SQL Server 2005.

Code Snippet

exec @.rc = sp_getapplock 'Only_this_SP', 'Exclusive', default, 0

if @.rc = -1

begin

-- SP is already running:

end

-- Release lock at end of SP:

exec @.rc = sp_releaseapplock 'Only_this_SP'