Home > T-SQL > SQL SERVER – Count the Open Transaction – @@TRANCOUNT @SQLSERVER

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.

Implementation

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
 
pic1

This script opened 3 transactions in the current connection. We can get the transaction count using @@TranCount.

pic2Now, I am going to roll back transactions and print the count.

pic4

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.

pic4I am going to show you how to display an open transaction information using DBCC command

pic5We have learned how to use @@Trancount to get active transaction count from the current SQL session in this blog post.

Advertisements
Categories: T-SQL Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: