How to check if stored procedure is already running?

• Will1922

  SSCertifiable

  Points: 7189

  More actions

  August 7, 2006 at 12:30 pm

  #173539

  I have stored procedure that I don't want 2 copies of it running at the same time.  How do I check if the stored procedure is already running in another instance? 

   

  I know that I could setup a table and just put a row in the table when it starts and empty it when I leave. 

   

  I want to know if there is a check that I can do on some system table that will tell me the same info.

   

  Thanks in advance for any responses to this post.   

  ---

  Live to Throw
  Throw to Live
  Will Summers

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  August 7, 2006 at 3:54 pm

  #653744

  Will,

  Check this thread on the same subject.  I assume you mean "session" when you say "instance"?

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=114165

  Greg

  Greg

• Ken McKelvey

  SSCoach

  Points: 18842

  More actions

  August 8, 2006 at 3:10 am

  #653784

  I have used application locks for this sort of proc. The idea is similar to using a table except everything is in memory. The outline code is:

  -- start of proc

  exec @err = sp_getapplock @Resource = N'YOURPROCNAME'

    ,@LockMode = N'Exclusive'

    ,@LockOwner = N'Session'

    ,@LockTimeout = 30000

  if (@err < 0)

  begin

   set @errorString = 'Failed to get exclusive lock.'

   goto onError

  end

  -- what you want to do...

   

  set @err = 0

  goto theEnd

  onError:

  raiserror(@errorString, 16, 1)

  theEnd:

  exec sp_releaseapplock @Resource = N'YOURPROCNAME'

      ,@LockOwner = N'Session'

  return @err