Completed Query still blocking?

Question

I ran into an issue in SSMS on a server running SQL Server 2017 where I had ran a query to update the owners of databases, and the query messages tab said that the query completed successfully. However, I was soon notified of issues with the server and to my surprise, the query was sleeping and blocking the db from being used. Killing the spid solved the issue, but how does something like this happen? Is there anything I can check for?

Answer 1

This will have happened if you opened a transaction and left it open and so the locks were never released.

You can reduce the probability of this happening by having SET XACT_ABORT ON.

This way any unhandled errors will rollback the transaction (many errors are not treated as automatically transaction aborting so after a failed run you may well not be aware that you have an open transaction already and even if you fix the error and run a BEGIN TRAN ... COMMIT the transaction is still open and all you have done is toggle @@TRANCOUNT).

If the above wasn't the cause then check the script for a mismatched number of BEGIN TRANSACTION and COMMIT statements, also check if your connection is using implicit transactions (as this is another way a transaction can get started non obviously)

Also you can get into the habit of closing down SSMS query tabs when done and it will notify you if there are any uncommitted transactions at that point (or simply running a SELECT @@TRANCOUNT to sanity check from that window)