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)
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 ????
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
declare @spid bigint
declare crsr cursor read_only
select spid from master..sysprocesses where dbid>1 and kpid>1
fetch next from crsr
fetch next from 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..