SQL SERVER – Quickest Way to Identify Blocking Query and Resolution – Dirty Solution

As the title suggests, this is quite a dirty solution; it’s not as elegant as you expect.

The Story:

I got a phone call at night (11 PM) from one of my old friends, requesting a hand. He asked me if I could help him with a very strange situation. He was facing a condition where he was not able to delete data from a table. He already tried to TRUNCATE, DELETE and DROP on the table, but still no luck. I demanded him to let me access it; however, he had to say “No” due to security reasons. Even though he really wanted my help, he was not authorized to even let me glance at his screen. After doing some background checks about the problem, I realized that he had open transactions somewhere, and this finally led to the solution of the issue. In his case, there was a transaction which was unnecessarily open, and was actually open for a long time now. It was safe for him to kill the transaction, so he killed it and everything moved on.

However, killing transactions can be too damaging to your server, so do not use this method; there are other (and safer) ways.

Here is the script we used to identify the blocking query.

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

The query above returned us the following results:

SQL SERVER – Quickest Way to Identify Blocking Query and Resolution – Dirty Solution block

In our case, we killed the blocking_session_id after carefully looking at the BlockingText; it was found to be not necessary at all. We killed the session using the following command:

KILL 52

As mentioned earlier, if you kill something important on your production server, there’s a great possibility that you’ll face some serious integrity issues, so I there’s no way I advise use this method. As the title goes, this is a dirty solution so you must utilize this only if you are confident.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Best Practices, Database, SQL Scripts
Previous Post
SQL SERVER – Error : Fix : Msg 5133, Level 16, State 1, Line 2 Directory lookup for the file failed with the operating system error 2(The system cannot find the file specified.)
Next Post
SQL SERVER – SQL Quiz – The View, The Table and The Clustered Index Confusion

Related Posts

56 Comments. Leave new

  • Thiago Anitelle
    March 9, 2018 8:57 pm

    Hello Pinal. Thanks for your great blog: no wonder it’s on the first page on Google when the search mentions ‘sql’.

    On your script, you use a field called “most_recent_sql_handle” to get either the request session text and the blocking session sql text.

    The point is: when a blocking session has submitted more than one statement, and the first statement aquired the lock on the resource (table, page or key/row) the script will only show the last submitted statement, which is not the one that we want to see.

    In this situation: how to locate the true statement that acquired the lock on the resource?

    Is is even possible taking into account that no traces or extended events are in use?

    Reply
  • Hi Pinal, This script worked great for me.. All I wanted to ask is there any option to get notification from SQL Server that regarding requesting and blocking sessions if they are running since very long time ?

    Reply
  • Hi Pinal, I use this query to identify blocks in SQL Server 2008 R2 version. I recently upgraded SQL to SQL Server 2016 version and this query takes long to run. Is there an update of this query for the SQL Server 2016 version?

    Reply
  • Hi Pinal, Thx for sharing such useful queries. Yesterday I faced one situation when sys.sysprocesses is showing blocking but this query is not showing any result. Actually one of our vendor was using this query and reporting no blocking but we always see some blocking. The wait resource is “APP: 6:0:[xxxxx.Schema.Lock.Migrations]:(asaasaad)” and lastwaittype “LCK_M_X”
    we are running sql 2019 ent ed with AG
    expecting some help
    thx
    Prabhash

    Reply

Leave a Reply