Transactions are the essential component to trace the database logs in the SQL Server. They are the single unit of work. SQL Server executes its all statements with the help of transaction logs. If the transaction is successfully committed, it becomes part of the database. Or if transactions encounter any type of error or get rolled back, then all modifications done will get eradicated.
Those transactions that are executing currently in SQL Server are Active Transactions. Monitoring active transactions are useful in determining the efficiency of databases. High number of active transactions indicates that certain transactions remain incomplete.
MS SQL operates in 3 different type of transaction mode:
- Autocommit transactions: In this type of transaction, each individual statement is an Autocommit transaction.
- Explicit transactions: In this, each transaction is explicitly started with the BEGIN TRANSACTION statement & explicitly ended with a COMMIT or ROLLBACK statement.
- Implicit transactions: In the implicit transaction mode, a new transaction is implicitly started after completion of previous transactions. But every transaction is explicitly ended by a ROLLBACK or COMMIT statement.
In this write-up,I will discuss how to check and kill active transaction in SQL Server.
How to Check Active transactions in SQL Server Database?
Command for identifying the current Active Transaction, that may prevent log truncation is:
DBCC OPENTRAN
The command displays information about the oldest active & distributed or non distributed replicated transcations present if any. The results will display only if any active transaction or any replication information is present in the transaction logs. If there is no active transaction present in the transaction logs, an informative message will be displayed.
Ways to Kill Active Transaction in SQL Server
To kill Active Transaction in SQL Server, you have to first find out the current running active transaction. For this, you have to find out the SP_ID of the active transaction so that it will display the oldest running transaction. The easy way is to run DBCC OPENTRAN.The command will show you the SP_ID of the oldest active transaction.
In order to kill active transaction in SQL server database, we have two different options. Either you can kill it by COMMIT/ROLLBACK operation or by simple executing KILL query. By COMMIT/ROLLBACK, you will be able to release active transactions. And by KILL command, you will be able to kill transactions according to SP_ID.
1.By KILL Command:
Kill it by executing KILL Command along with the transaction log session ID:
kill (sp_id)
go
exec (tran_name)
go
Now execute your query and see the result.The currently running Active transaction will get aborted.
Now, we will understand how can you kill your transaction by COMMIT/ROLLBACK Operations.
2.By COMMIT/ROLLBACK:
SQL Server operates in Auto Commit Mode. However, by using SET IMPLICIT_TRANSACTIONS ON command, you can override the automatic commitment so that SQL Server will wait for you to issue an explicit COMMIT or ROLLBACK statement to do anything with the transaction. Execute the following command:
Commit Transaction
The Bottom Line
In this blog, we have discussed what is an active transaction in SQL Server and how to kill active transaction. We also covered three different transaction modes to view active transaction in SQL database and a suitable method to identify the active transaction in SQL Server database.
EmoticonEmoticon