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:

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

  • Thanks Pinal, save my day today this script! Just searched and the needed for a blocking process.

    Reply
  • Vesselin Vassilev
    June 4, 2014 10:42 am

    Thanks, Pinal! Much appreciated!

    Reply
  • Rahul Dewada
    June 27, 2014 3:32 pm

    Thank you Pinal. Very helpful post

    Reply
  • Pradeep Kumar Gupta
    August 4, 2014 2:48 pm

    A lot thanks Mr. Pinal for this post. Really very helpful

    Reply
  • Hi,
    how can i run this script on all databases?
    THX

    Reply
  • Indeed a helpful query. I have a weird situation. This query prints out 50-60 selects, and one specific select is blocking the other selects. What could be wrong, and how to fix such a situation?

    Reply
    • Bela – First you need to find the wait resource. Then you find the session which is “head” blocker and check what it is doing.

      Reply
  • you can check DBCC inputbuffer(sesstionID) what SP it is.

    Reply
  • Works great! Added login/connection info:

    SELECT db.name DBName,
    tl.request_session_id as ReqSessionId, esr.host_name as ReqHost, esr.program_name as ReqProgram, esr.client_interface_name as ReqClient, esr.login_name as ReqLogin,
    wt.blocking_session_id as BlkSessionId, esb.host_name as BlkHost, esb.program_name as BlkProgram, esb.client_interface_name as BlkClient, esb.login_name as BlkLogin,
    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
    join sys.dm_exec_sessions esr on esr.session_Id = tl.request_session_Id
    join sys.dm_exec_sessions esb on esb.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

    Reply
  • it is not working well in SQL 2014 , query keeps on running and running

    Reply
  • I’m facing the same issue because it is continuously running.

    Reply
  • This help me too
    very appreciate

    Reply
  • i have a session ID, and machine name, i want kill the particular session which is blocking more than an hour, without restarting Application.

    Reply
  • Confirmed the query does NOT work in SQL Server 2014.

    Reply
  • Hi Edward I tried and it is working in SQL Server 2014.

    Reply
  • Hi,

    I too have tried this in SSMS 2014 against a 2012 DB and it is not finding blocking sessions whereas they are visible in Activity Monitor.

    Thanks,

    Reply
  • instead of killing spid is there any option

    Reply
  • Andrew Sergeev
    May 3, 2017 4:48 pm

    Don’t trust BlockingText. As it shows only statements of the last batch. Who knows how many batches have been executed in this spid earlier.

    Reply
  • Christian Daneth C G
    June 22, 2017 10:05 pm

    Gracias Pinal

    Reply
  • doesnt work for me in higher version of sql..is there an update…

    Reply
  • I am running a script that creates clustered indexes and it is blocking others (Ver 2014). Tried to use your query and it is being blocked as well. Resorted to old sys.sysprocesses and dbcc inputbuffer.

    Reply

Leave a Reply

Menu
Exit mobile version