This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to find the all open transactions in SQL Server.
If your server has unwanted open transactions, it degrades the performance of other dependent transactions.
There are many ways to find open transactions in SQL Server.
Here, I used sys.dm_tran_database_transactions view to find out open transactions.
Below is a script:
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read only transaction'
WHEN 3 THEN 'System transaction'
WHEN 1 THEN 'Transaction not initialized'
WHEN 3 THEN 'Transaction has not generated by any log'
WHEN 4 THEN 'Transaction has generated by log'
WHEN 5 THEN 'Transaction Prepared'
WHEN 10 THEN 'Transaction Committed'
WHEN 11 THEN 'Transaction Rolled back'
WHEN 12 THEN 'Transaction committed and log generated'
FROM sys.dm_tran_database_transactions tdt
INNER JOIN sys.dm_tran_session_transactions tst
ON tst.transaction_id = tdt.transaction_id