Concurrent stored procs?

Hi all

I have a stored proc that runs every 4 hours - as a job. The stored proc takes about 3-5 minutes to comple, depending on number of records.
To do testing we run that stored proc manually, also.
Sometimes 2 or more people may run the same stored proc without knowing that the other person is running. This may create duplicates entries once processed.

What I want to know is, Is there a way to check if that stored procedure is currently running. If so it wont run second time concurrently.
(May be semapohres,mutex or something like that?)

(I am trying not to use a table to store whether the stored proc is running or not)

Thanks in advance.

RochanaOne can use sp_getapplock and sp_releaseapplock when invoking a sp.

Hans.|||Thanks Hans for quick reply.

I tried it but found some problems.
sp_getapplock needs an active transaction, without which it fails.
I am trying not to use Transactions because it slows down the system so badly.

Originally posted by HansVE
One can use sp_getapplock and sp_releaseapplock when invoking a sp.

Hans.|||Could use global temporary table as semaphore.

CREATE TABLE ##proc_running(x int)
IF @.@.error <> 0
PRINT 'Proc already running'
ELSE BEGIN
...
DROP TABLE ##proc_running
END

Hans.|||It works to some extent Hans.
The error checking never happens. It quits the process without displaying the error.
..
IF @.@.error <> 0
PRINT 'Proc already running'
...

Originally posted by HansVE
Could use global temporary table as semaphore.

CREATE TABLE ##proc_running(x int)
IF @.@.error <> 0
PRINT 'Proc already running'
ELSE BEGIN
...
DROP TABLE ##proc_running
END

Hans.|||Yes, the error is too serious to continue. If you cannot trap that error at the client, you could instead check for existence of the table object.

IF OBJECT_ID('tempdb.dbo.##proc_running') > 0|||sysprocesses holds info about all processes currently running on the server (master.sysprocesses)

dbcc inputbuffer spid tells you what a specific process is doing

The two combined (in some way ;)) should tell you if the proc is running already..

Hope it helps a little bit..|||I am a little confused .. but isnt the job scheduled ... then why is it being run manually ... and even if it is being run manually ... why two people have been given the access ??|||Originally posted by Jonte
sysprocesses holds info about all processes currently running on the server (master.sysprocesses)

dbcc inputbuffer spid tells you what a specific process is doing

The two combined (in some way ;)) should tell you if the proc is running already..

Hope it helps a little bit..

One problem with this is that two people could still start the proc simultaneously.

Hans.|||Enigma here is the scenario:
Say, the job is scheduled at 12noon everyday.
One of the bosses come and asks us to run that particular job becos they need to see the data on their screen. So one of the programmers of the team runs that job, or invoke the particular sp.
The sp takes about 10-15 mins to complete.
If it reaches 12noon while that particular sp is running, job kicks in and invoke that same sp again. (our sp is still running)
Thats why I want to check if the particular sp is already running or not, so that the sp wont run again.

Originally posted by Enigma
I am a little confused .. but isnt the job scheduled ... then why is it being run manually ... and even if it is being run manually ... why two people have been given the access ??|||Thanks Jonte..
Your suggestion worked !! ;)
I have to check the Event Info for my stored proc, thats running in the server.

I have pasted code to test what processes are currently running on the server on a db. May be you can run and see the results too

Thanks

declare @.spid bigint
declare crsr cursor read_only
for
select spid from master..sysprocesses where dbid>1 and kpid>1

open crsr
fetch next from crsr
into @.spid

while @.@.fetch_status<>-1
begin
dbcc inputbuffer(@.spid)
fetch next from crsr
into @.spid

end

close crsr
deallocate crsr

Originally posted by Jonte
sysprocesses holds info about all processes currently running on the server (master.sysprocesses)

dbcc inputbuffer spid tells you what a specific process is doing

The two combined (in some way ;)) should tell you if the proc is running already..

Hope it helps a little bit..