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?

1 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)

  • Thanks very helpful. The only thing is this query was running across multiple servers at the same time, yet it was only one server that this issue occurred on. I would think it would have happened on the other servers right? That's what's bugging me about this.
    – DBANoob
    May 31 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.