Checking blocked transactions in SQL Server

This is probably very well known, but I think it’s an important part of a developers arsenal.

Imagine this scenario:

Your application fails to perform its actions. You may even see an error similar to “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

The probable cause is that either the database cannot keep up with the number of requests, a particular request has taken too long, or a rogue transaction is hogging resources.

If you suspect the third possibility, you can check by using the following command

sp_who2

The output will provide you will something similar to the screenshot below
ResultSet

Any transaction that is causing a block on another transaction will appear in the “BlkBy” (a.k.a. Blocked By) column.
You can then issue a Kill command to end the transaction.

Kill 10

 

Subscribe

0 comments