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
Pinal, thanks for this helpfull post.
I am confused, would not DBCC OPENTRAN get him the same results?
I’m trying to run the query on SQL Server 2008 and it is breakingon the CROSS APPLY line in the predicate – not sure what this is
Thanks.
Not sure if I got this from you too, but our dirty solution for the same problem is finding which connection has been running for long time.
Probably this only works well with small, simple and low traffic implementations.
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
@bandit
check the compatibility on the database, should be 90 or higher
If this is once a blue moon blocking, you might kill it and call victory. However, I suggest readers first understand the implications.
1) If this is a web app, when you kill it, the user on the other end of the world might re-submit again after seeing execution unsuccessful. Blocking occurs again. You are tracing tail.
2) If the code has fundamental issues, you need to identify the code issue and point out to developers to fix. Kill it in reaal-time won’t do anything good in the long-run.
Please visit my SQLSaturday#57 presentation which covers blocking.
If you are killing the same job, same code execution, you better stop and find out what is wrong with that code.
i get the session blocking because one Procedure is blocking to other Procedure.
After looking into the procedures we found that the select query in both the procedures uses the same function and the joins are on same tables in both procedures.
These two procedures were executing at same time and hence the blocking issue occurred.
Can u tell me the possible solution on this issue ?
Thanks a bunch, that saved me a fair bit of time. How many years have you spent learning Microsoft SQL? You so are an internet high-and-mighty at it!
Thanks again, Garry.
Hi Pinal,
I was testing your query and found that it didnt give me the results that I was expecting.
To reproduce, I start a transaction and did an insert. Now in another query window i go and do a select on the same table.
Now if I run the query I am not able to see any results,
Due to the fact that in sys.dm_os_waiting_tasks we dont see the spid which is actually blocking. It is the same case even if you do query sys.dm_exec_requests.
Hello Mate,
I did test this query but gave me no results but I started a open Transaction and run a select query on different window, I know select query was waiting for the open transaction to finish, but when I run the query provided, Gave me no results.
This will gives us the full text (Eg,. For an SP it will show us the full code) is there any way to get the only statement causing blocking along with the SP name?…
Hi,
I am using following method for finding the blocked processes.
SELECT spid FROM master..SYSPROCESSES WHERE blocked0
Let’s say 56 is the spid blocking
DBCC INPUTBUFFER(56) — Will give you the Event Info
KILL 56
But deepkt if any job running for above spid56 then if u kill means
that job will fail and create problems..so before killing any process just look at what it is doing and decide which one to kill either 52 or 56..
thanks
hi,
i usually do the same thing mentioned above when the situations happens.
But i want to automatically kill the procees if any long running queries occur.
What can i do??/
Wow…. thank’s!!!!
This is awesome! Thank you!
Try this…
sp_who2 active>>> will result with the spids, and other details and has a column showing the blocking process if any.
then use DBCC inputbuffer ()
if not a dml (to prevent data inconsistency, we should avoid killing a insert, update or delete), use kill
This will kill the blocking process and the resource/object will be realeased hence resolving the blocking
Can you tell; us how to reproduce it and test it?
Hi Pinal,
The above query for identifying blocking is working but this information is dynamic, but what if we are getting block somewhere around midnight and get a mail in the morning saying there was a blocking at midnight please check.
How can we record blocking history is my question.
Thanks
Karthik
Hi Pinal,
Good Morning !!!
i am supporting SQL server 2005 .There are around 100 SQL jobs which used to be scheduled to run at its time on daily, weekly, weekday and Monthly basis.
But, i would like to bring to your notice that there are some SQL jobs which are taking more time to execute than expected.
we would like to fix this issue asap to ensure that all the SQL jobs will be running on its schedule timings without long execution.
Please let me know the what are all the tables need to be checked during the long execution of SQL jobs
Hi Pinal,
In our organization we are running an ERP Software. This software was developed using ASP.NET as Front End and SQL Server 2005 as Back end… When more number of users accessing at the same time means we are facing slowness in Execution… Some times SQL server gets Hang… What we have to do to overcome this problem… Pls do the needful
I found the INNER JOIN to sys.partitions is problematic. If the block is on an object, then it needs to go against sysobjects. Seems we need to case out a join if resource_type = object then sys.sysobjects …