Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    11

    Question Unanswered: 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.

    Rochana

  2. #2
    Join Date
    Mar 2004
    Posts
    45
    One can use sp_getapplock and sp_releaseapplock when invoking a sp.

    Hans.

  3. #3
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    11
    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.

  4. #4
    Join Date
    Mar 2004
    Posts
    45
    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.

  5. #5
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    11

    Question

    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.

  6. #6
    Join Date
    Mar 2004
    Posts
    45
    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

  7. #7
    Join Date
    Feb 2002
    Location
    Sweden
    Posts
    34
    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..
    - Jonte

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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 ????
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Mar 2004
    Posts
    45
    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.

  10. #10
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    11
    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 ????

  11. #11
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    11

    Thumbs up

    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



    Code:
    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..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •