How to find active open transactions in SQL Server 2008R2

Friends,

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
• 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.

From the above Pic 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,’Roopesh’,’PL’,’IT’)
insert into Employee Values(2,’Lokesh’,’PM’,’IT’)

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

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 and there is one more way i.e by using the following SYSTEM tables.

select * from sys.dm_tran_active_transactions
select * from sys.dm_tran_database_transactions
select * from sys.dm_tran_session_transactions

The above tables stores the information about ACTIVE Transactions,Database Transactions and Session Transactions respectively. You can make use of these queries to get MORE details about OPEN and ACTIVE transactions.

SELECT
trans.session_id as [Session ID],
trans.transaction_id as [Transaction ID],
tas.name as [Transaction Name],
tds.database_id as [Database ID]
FROM sys.dm_tran_active_transactions tas
INNER JOIN sys.dm_tran_database_transactions tds
ON (tas.transaction_id = tds.transaction_id )
INNER JOIN sys.dm_tran_session_transactions trans
ON (trans.transaction_id=tas.transaction_id)
WHERE trans.is_user_transaction = 1 — user
AND tas.transaction_state = 2 — active
AND tds.database_transaction_begin_time IS NOT NULL

So .. That’s it and now you can track the open transactions easily .. 🙂

All the Best !!

Regards,
Roopesh Babu V