As the title suggests, this is quite a dirty solution; it’s not as elegant as you expect.
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.
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
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
The query above returned us the following results:
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:
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)
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?
I don’t think you can catch previous statements as they may not be seen in DMVs
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 ?
You need to put job to run on regular basis and send email if blocking is found.
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?
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