Display Open Transaction in SQL Server

Fetch open transactions in SQL Server:

We will face an usual problem in transactional queries, Open a transaction and forgot to commit or rollback. This creates lot of issues like,
1. Your transaction wont get committed or reflected.

2. Sometimes, the table will get locked and other transaction cant use the table results in deadlock.

3. You log file will be flooded and if its full, It will hang the entire database.

To get all the transactions available in the database use the below command,

DBCC OPENTRAN

BEGIN TRANSACTION
insert into VenkatSample values(100,'Karthi','Karthi')

DBCC OPENTRAN

The above command will list down the transactions pending for commit or rollback. On restarting the server, those transactions will get rollbacked in turn everything will be flushed.

Below DMV will provide the transactions happened in this particular session,

select * from sys.dm_tran_session_transactions

sys.dm_tran_database_transactions will provide all the transactions stored in the database.

Happy Learning!!!

Cheers,

Venkatesan Prabu .J