SQL SERVER – Count the Open Transaction – @@TRANCOUNT @SQLSERVER
@@TRANCOUNT returns the count of open transactions in the current session. It increments the count value whenever we open a transaction and decrements the count whenever we commit the transaction. Rollback sets the trancount to zero and transaction with save point does to affect the trancount value.
It helps application to keep track of the open transactions. There are other ways to get the active transaction in SQL Server.
DBCC OPENTRAN displays information about the active transaction of the current database.
Following script insert rows in to a table with transaction block and print the count of open transactions in the current session.begin tran Insert into tblRowVersion (rowid) values (4) GO 3
This script opened 3 transactions in the current connection. We can get the transaction count using @@TranCount.
It roll back all open transactions in the current session and decrement the trancount to 0. In the next example, I am going to show you how commit statement decrement value from trancount.