April 30, 2009 at 2:52 am
Does anyone know how to check if a stored procedure is currently running or not? I'm writing an application where it is important not to start executing a second time if the sp is already running.
April 30, 2009 at 4:02 am
I am not sure this can be done using simple methods, though can be achieved by using complex approaches such as application locks, system tables or ole automation objects. These are highly complex techniques and are not recommended.
Another approach is to store the status of the procedure execution in a table and in the procedure check for the run status from this table before proceeding further.
--Ramesh
April 30, 2009 at 4:14 am
i was also thinking about the latter solution, but wondered if there weren't other solutions
thanx, ill give it a try!
April 30, 2009 at 6:11 am
Sure. Check sys.dm_exec_requests. You should be able to get what you need from there or by joining to one of the other execution DMV/F's to prevent a secondary execution while the procedure is running.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 30, 2009 at 7:12 am
See this article[/url] on SQL Server Central by Ian Stirk for some ideas and example code.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply