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
(...)

Read more: Journey to SQL Authority with Pinal Dave

Posted via email from .NET Info