Post reply

sp_getapplock issue

  • April 13, 2011 at 8:33 am

    #389268

    hi,

    Background:

    DB Instance supporting Our BizTalk server is having a scheduled job for taking full and trans backup.

    Support members from Microsoft have used some stored procedure to take backups (during the implementation phase)

    Issue:

    this job is currently running infinitely. while analysing the stored proc provided by MS team, I found

    sp_getapplock stored procedure is running infinitely resulting backup job failure and other inconsistancy in BizTalk environment.

    can anyone of us clarify

    1. what is the purpose of sp_getapplock

    2. how we can troubleshoot if sp_getapplock is running for long time.

    thanks in advance

  • April 13, 2011 at 9:36 am

    #1311103

    1. sp_getapplock applies a user defined application lock. It saves an applicaiton having to cope with locking tables.

    2. As sp_getapplock is waiting indefinately, SET LOCK_TIMEOUT must be set to -1 (the default).

    I suspected a session application lock has been set in the code with no corresponding sp_releaseapplock.

    (This may have happened due to poor error handling.)

    Look for something like:

    EXEC sp_getapplock 'ResourceName', ' Session'

    there should be a corresponding:

    EXEC sp_releaseapplock 'ResourceName'

    If there is no sp_releaseapplock, you will need to add it.

    If there is a sp_releaseapplock, you will need to check it is run if an error occurs.

    All locks will be cleared if you re-start SQL Server.

  • April 17, 2011 at 12:40 am

    #1312493

    Thanks Ken

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply