This set of procedures allow you to control simultaneous access to any resource you are using. It mimics the behaviour of a Semaphore in programming.
A typical problem where you need this, is when you have a computational intensive procedure you only want to be started a limited number of times.
First, add a record to the Semaphore table, identifying the maximum number of simultaneous 'users' of the resource.
Whenever you need access to that specific resource, you add a call to usp_LockSemaphore to the beginning of your call. If the procedure returns 0 (zero), you can continue using the resource.
After the resource is no longer used, call usp_UnlockSemaphore.
Based on the script by Paul Delmarche ( http://www.sqlservercentral.com/scripts/contributions/1482.asp ).Updated to now include the spid, username, blocked and blocking SQL statements in the email (still logged to table).Schedule the procedure to run at defined intervals (I use 2 minutes)