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)
56 Comments. Leave new
Thanks Pinal, save my day today this script! Just searched and the needed for a blocking process.
Thanks, Pinal! Much appreciated!
Thank you Pinal. Very helpful post
A lot thanks Mr. Pinal for this post. Really very helpful
Hi,
how can i run this script on all databases?
THX
This query will be executed on MASTER Db so indeed it will capture all database’s blocking.
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?
Bela – First you need to find the wait resource. Then you find the session which is “head” blocker and check what it is doing.
you can check DBCC inputbuffer(sesstionID) what SP it is.
My query would tell you statement. DBCC INPUTBUFFER is old way.
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
Thanks for the comment!
it is not working well in SQL 2014 , query keeps on running and running
I’m facing the same issue because it is continuously running.
This help me too
very appreciate
Thanks Tochsaporn
i have a session ID, and machine name, i want kill the particular session which is blocking more than an hour, without restarting Application.
you need to write a script and put that in a SQL Agent job.
Confirmed the query does NOT work in SQL Server 2014.
Hi Edward I tried and it is working in SQL Server 2014.
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,
instead of killing spid is there any option
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.
Gracias Pinal
doesnt work for me in higher version of sql..is there an update…
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.