4

My team and I have an issue where we need to be able to identify processes that are blocking other processes and kill them. There are a ton of scripts that are freely available to perform these actions. We have tried various ones and scrutinized the code. (If you are someone that has posted one of these queries, thanks!)

Before I go further, let me tell you that this is against a vendor application that we cannot modify. The vendor is also not willing to spend the time to figure out why processes are getting blocked. The only choice we have at this point is to kill the long running processes (as suggested by the vendor support). Before killing these processes, we do review the query running, but 99.9% of the time, it shows up as FETCH API_CURSOR00000000000A7E1F, which tells us nothing.

This has been a manual process up to this point. Now, we want to automate the killing of these long running blocking processes instead of someone manually killing them.

We want to test this script before putting it into production. We would like some help creating a script that would intentionally create blocking processes. We have tried with the TEST environment with this application, but unfortunately, we have had no luck replicating blocking processes.

Thanks in advance for your assistance!

1
  • If you know the Spid which is causing g the block, have you queried sp_lock to find the object/level it has acquired a lock on and what is waiting on it? This should provide precious clues as to what your problems are.
    – Molenpad
    Commented Jul 21, 2016 at 19:21

3 Answers 3

9
BEGIN TRANSACTION
  SELECT * FROM YourTable WITH (TABLOCKX, HOLDLOCK)
    WAITFOR DELAY '00:05:00' -- 5 minutes
ROLLBACK TRANSACTION

And then in another Query Window:

SELECT * FROM YourTable
1
  • This doesn't work for me, possibly due to snapshot isolation or read committed snapshot being enabled on the DB.
    – Josh
    Commented Aug 13, 2021 at 13:40
4

This is pretty easy to do. Open two query windows in Management Studio.

Run something like this in the first one (choose a test table that nobody is using, because this will block them, and make sure it has at least one row in it):

BEGIN TRANSACTION
SELECT * FROM sometesttable WITH (TABLOCK, XLOCK, HOLDLOCK)

Then in the second query window:

SELECT * FROM sometesttable

Your second query will be blocked until the first one is either committed or rolled back.

1
1

You could also fire this off in a few concurrent sessions.

Only one session will be able to get the exclusive lock at any one time.

BEGIN TRANSACTION;   
EXEC sp_getapplock @Resource = 'FooBar',   
               @LockMode = 'Exclusive';  
WAITFOR DELAY '00:00:30';
EXEC sp_releaseapplock @Resource = 'FooBar';  
COMMIT TRANSACTION;  
1
  • 1
    I guess the OP might need to adjust the delay to match their definition of "long running", so that the process is caught before it goes away.
    – Andriy M
    Commented Jul 22, 2016 at 6:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.