I have been struggling with database blocking at my database server from last so many months. Have tried to use many ways to capture them and also aware that who are leaders of blocking. This application uses typical primary key-foreign key relationship and all the tables are tightly coupled. Few tables have as many as 30 foreign keys and hence whenever there is an insert/update/delete, all of those underlying tables get blocked. I have communicated this to vendor and they also have tried to remove PK-FK for possible tables and deployed in the test environment however its taking ages to go to production as lot needs to be tested and its failing in some or other scenario.

Since, I am supporting vendor based application and any change needs to be tested by multiple stakeholders, I don't know when will it really go to production. Blocking becomes so severe at time that whole portal goes down and I am left with no option but to kill the blocking SPID.

I am using below query to check the blocking by using sp_whoisactive procedure Mr. Adam Machanic:

EXEC sp_WhoIsActive
    @find_block_leaders = 1,
    @sort_order = '[blocked_session_count] DESC',

So far, I was working on reactive approach meaning, I was killing SPID who are responsible for blocking however this doesn't seem to go well and sometime blocking escalates and I have to run like fire-fighter. I would like to switch my side from reactive to proactive and planning to put a procedure in place which will take input as SPID from above procedure, check the duration and if blocking is more than 5 minutes, kill it.

At times, I have seen there are many blockers responsible for blocking as shown below:

Blockings output of sp_whoisactive

Don't really know how will these kind of blocking be handled. Read something similar by Mr. Jason Hall at this link and second one at this forum however I am looking for something having base of sp_whoisactive.

This is to add very pertinent point about database isolation that I have changed database isolation level from default to "Read Committed Snapshot". On the very same topic, I had asked this question when I joined this forum however it was closed as I was very new to this forum and was unclear on my question.

Any input/help on this will be appreciated.

Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 14393: ) (Hypervisor)

  • 1
    Have you looked at optimistic isolation levels such as READ COMMITTED SNAPSHOT or SNAPSHOT ISOLATION which may help avoid these large chains of blocking queries outright? These changes do have caveats though, so do some investigation and testing in non-production environments before making any changes to your live system. Commented Apr 8, 2019 at 13:59
  • @JohnEisbrener That was the first thing I did after joining this company, change isolation level from default to read committed snapshot. Commented Apr 8, 2019 at 14:14
  • So READ COMMITTED SNAPSHOT is enabled and you're still getting this behavior? Can you update your question with this information and then provide some more context around the queries causing this behavior? Commented Apr 8, 2019 at 14:31
  • @JohnEisbrener Updated the question with isolation level, as you could see in the screenshot - writer is blocking writer and there are lot of foreign keys in the table. That seems to be the culprit. Since, Its taking too long to check and fix, I am looking for some script/procedure to find the blocking spid and kill it if it stays on the server for more than 5 minutes.(Not a very good approach though but, can't hep it). Commented Apr 8, 2019 at 14:37
  • If possible, listing the queries of the lead blocker as well of a few of the blocked queries would be helpful as well. Commented Apr 8, 2019 at 14:46

3 Answers 3


You can use the SQL script I wrote up below to build a dynamic SQL string that will kill all blocking sessions that have been running for at least 5 minutes.

You'll want to create a SQL Agent job (SQL Agent Job Docs) that executes this SQL script. (You can schedule the SQL Agent job to run as often as every second if you want, but the Agent Job GUI only allows you to select a minimum of 1 minute when scheduling, so if you need greater frequency you have to script the job out.)

-- Variable to hold dynamic SQL of what blocking sessions will be killed

-- CTE of blocking sessions
WITH CTE_BlockingSessions AS
    SELECT blocking_session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0

-- Sets the @DynamicSQL variable = 'KILL ' + all blocking session IDs that have been running for at least 5 minutes
SELECT @DynamicSQL = @DynamicSQL + 'KILL ' + CAST(BS.blocking_session_id AS VARCHAR(10)) + ';'
FROM sys.dm_exec_requests AS ER 
INNER JOIN CTE_BlockingSessions AS BS
    ON ER.session_id = BS.blocking_session_id
WHERE DATEDIFF(s, ER.start_time, GETDATE()) >= 300 -- 300 seconds = 5 minutes
    AND ER.[status] = 'RUNNING' -- Ensures the blocking session is still active

-- PRINT @DynamicSQL -- For testing, outputs the dynamic SQL to SSMS Messages tab
EXEC sp_ExecuteSQL @DynamicSQL -- Executes the dynamic SQL

--- Get list of running Processes with greater than 5 mins

SELECT  spid,
 DATEDIFF(MI, last_batch, GETDATE()) EventTime ,
          --Transactions that are open not yet committed or rolled back
          WHEN Status = 'SLEEPING' AND open_tran  > 0 THEN 1 
          ELSE 0 END
AND DATEDIFF(MI,LastBatch,GETDATE())>5 -- greater than 5 mins

--- Get detail against each SPID retruned by the above query


--- KILL the process if it has to be


NB Last_Batch not LastBatch

  • 1
    link-only answers aren't really seen as acceptable on DBA.SE - could you add some more detail as to why you think the link will help? It may also be useful to directly copy the relevant information from the article into your answer. Commented Apr 8, 2019 at 11:47
  • I assume that the recommendation to directly copy test from an article into a reply only applies to when you yourself are the author of that article? I.e., we don't encourage "stealing" information on the net...? Commented Apr 8, 2019 at 12:07
  • 4
    @TiborKaraszi absolutely it does not - As long as you credit the author, it's much better to have the text itself on SE. What if the original author removes the post? This post is then completely useless. The advice I've always been given is copy as much out of the article as needed to ensure your answer will always be helpful. Commented Apr 8, 2019 at 12:11
  • @user164767 - Thank you for response. This will work when checked manually and killed however if there are multiple blocking, how will it get all of them? Commented Apr 9, 2019 at 3:00
  • @user164767 - I tested this and it doesn't seem to be working.We should be targeting the lead blocker and also should check for the lock type. Commented Apr 9, 2019 at 5:21
  1. Try with NOLOCK it may solve your problem. It will probably will not create any type of locking by executing Select query.

  2. Disadvantages of NOLOCK, you may face dirty read problem. so use NOLOCK very carefully.

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.