How to find OPEN and ACTIVE transactions in SQL Server?

This is a very common question of every DBA that how do I know if there is any open transaction in my database? I recently wanted to restart my server, but I wanted to check if there is any open transaction in database before I restart the system.

In this post we are gonna discuss about the way to find the OPEN and ACTIVE Transactions in Sql Server.

To explain the same I created a table with the below given script.

Create table Employee
(
Emp_ID int,
Emp_Name Varchar(50),
Dept Varchar(50),
Desig Varchar(50)
)

To check the Open Transactions you can follow any one of the following ways.

select @@TRANCOUNT
<pre>DBCC OPENTRAN

Both the above Sql statements returns the Open transactions details. The first one gives you the count of OPEN transactions where as the second gives you the details of the open transaction. First let me execute the same and see whether any open transactions are available.

I am getting the following message when I execute DBCC OpenTran query.

“No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.”

and when I execute the second one i.e “select @@TRANCOUNT” statement, I get 0.

How to find OPEN and ACTIVE transactions in SQL Server?

How to find OPEN and ACTIVE transactions in SQL Server?

From the above image and the Error Message, it is clear that there are NO ACTIVE OPEN transactions. Now let me execute the following query which OPENS the transaction but not Close.

Begin Tran
insert into Employee Values(1,'Tushar','Install', 'Mentor')

The query executed successfully and now lets execute the above commands to check the status of the open transactions.

How to find OPEN and ACTIVE transactions in SQL Server?

You can see that the command is proving the open transaction details. Now let me execute the following command to Commit the transaction i.e Active.

Commit tran

Now if I execute the DBCC command again, you can see the following message which says that there are NO OPEN TRANSACTIONS.

“No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.”

This is how you can get the details about the open transactions.


<<Click here to see all posts>>

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.