sp_releaseapplock timeout expired cause?

Posted by Darian Miller on Server Fault See other posts from Server Fault or by Darian Miller
Published on 2010-05-12T20:24:44Z Indexed on 2010/05/12 20:34 UTC
Read the original article Hit count: 270

Filed under:

I've been using a combination of sp_getapplock and sp_releaseapplock for some custom application locking purposes for years now with success and just the last few days I've started to get some timeout expired errors on a sp_releaseapplock which is a bit puzzling. When listing the current locks, there's less than a dozen of these active, and the rest of the dedicated server is way underutilized at the moment (less than 100 batches/sec with a mutli-processor, 32GB Ram, higher end machine.)

Is there a specific resource to be monitored that may point me in the right direction for determing why such a lightweight operation is timing out? This is called within a stored proc with a timeout of 120 seconds which seems to be amazingly long for this operation to timeout on.

SQL 2000 SP4 running on Windows 2003 Server.

TSQL used (@pLockUniqueName is VarChar(255))

EXEC @pLockSuccess = sp_getapplock @pLockUniqueName, 'Exclusive', 'Session', 0 EXEC @pUnLockSuccess = sp_releaseapplock @pLockUniqueName, 'Session'

Thanks,

Darian

© Server Fault or respective owner

Related posts about sqlserver-2000