SQL Server Blocking

Question

MSSQL block issue.

I queried sys.sysprocesses and find the head blocker session ID. Then as provide by MS before, I ran DBCC inputbuffer, nothing returned. I suddenly notice the session status is waiting command but open tran is 1. What is the cause.

Answer 1

Hi @Elon Musk,

If the session status is "waiting command" but the "open tran" status is 1, it means that the session has started a transaction but is currently waiting for a command to be executed within that transaction.

In this case, it is possible that the transaction has not been fully committed or rolled back, and as a result, it is blocking other sessions from accessing the resources it is using.

Please refer to this thread:

https://stackoverflow.com/questions/41078457/how-to-check-blocking-queries-in-sql-server

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Thanks for your question.

Kindly find the blocking query in the thread below.

When blocking occurs, execute the query to determine the lead blocker and the precise statement that is causing the blocking.

https://learn.microsoft.com/en-us/answers/questions/1180235/blocking-gives-me-wrong-impression-in-this-scenari

Thank you!

Answer 3

How can I avoid it happening again

Answer 4

How can I avoid it happening again

The symptom of a transaction open on a sleeping connection for a long period may be because an error occurred and the transaction was not rolled back. One scenario where this happens is when a transaction is started in a stored procedure and the command times out. The transaction won't be rolled back until the pooled connection is reused or closed.

This problem scenario can be avoided by adding SET XACT_ABORT ON; to the stored procedure to ensure the transaction is rolled back immediately when a timeout occurs (i.e. cancelled by the client application).