Transaction Locked Down SQL Server 2005

Question

I have noticed something over the last few months. Each time we run a script that has a transaction statements let say we stop the query unexpectedly this action actually lock the database.

The only way out is to destroy the transaction each time. I have never experience this before even though I have stopped the query in the middle of transaction in the past and it has never locked the database.

Could it be that we are missing something in settings or I should not stop transaction queries unexpectedly?

The problem occurred with SQL SERVER 2005. please I need your brain. Thanks Guys

Answer 1

This is usual: you have sent a client abort which say "stop processing"

To rollback and release lock, you need to use SET XACT_ABORT ON

SO 1 and SO 2