Sql-server – Need to intentionally create blocking processes for testing

blockingprocesssql serversql-server-2008-r2

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!

Best Answer

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