Introduction
While working with Microsoft SQL server database, there are many scenarios where database tables are being blocked/locked. An expected small result set from a SELECT
statement, takes ages. An UPDATE
statement for a single row runs forever. Most of the time, developers were not able to know the reason. I have seen people restarting the database service to fix these types of issues, but that is not correct. We need to find the real reason of blocking and how to unblock it.
Background
Before knowing how to fix, we need to know why blocking occurs in database. When we connect to a SQL database or application connects to the database, each connection appears as a session for the database. Hence, each session is provided with a unique id which is called as Session ID (SPID). So when one connection holds a lock to the table and if at the same time another session requires a conflicting lock, blocking occurs. For example, we are running a script under transaction and forgot to rollback or commit the transaction. Sometimes, when we cancel an execution running under transaction creates these types of problems.
To unblock a table, we need to know the session id that holds the transaction on the table. We can follow the below steps for the same.
All blocking Transaction Report
In SQL Server Management Studio, go to Object Explorer and open the SQL instance. Select the database where we have the table being blocked. Right-click on the database > Reports > Standard Reports > click on All Blocking Transactions.
It would show us all blocking transactions on the database. From that report, we can get the SPID, script causing the blockage and the scripts being blocked.
Transactions being blocked
To know who is the culprit, we can take the [SPID]
and execute SP_WHO2 [SPID]
. As per the example in the screenshot, it should be SP_WHO2 52
. It will provide the Login, Hostname (system name from where the command is executed) and the Program Name (application from where the command is executed). This information is enough for us to find who is causing the blockage. We can kill the transaction by using KILL [SPID]
. As per the example, it should be KILL 52
. This would eventually rollback the blocking transaction.
Status bar showing the SPID
Else, we can check the management studio of the user and find the exact SQL query window by checking the 3rd section of the status bar where it shows the SPID
with the user log in. So either we need to rollback or commit the transaction to fix the issue.
I have one copy of the same tips on my own Wordpress.