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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SELECT tdt.transaction_id ,tst.session_id ,tdt.database_transaction_begin_time ,CASE tdt.database_transaction_type WHEN 1 THEN 'Read/write transaction' WHEN 2 THEN 'Read only transaction' WHEN 3 THEN 'System transaction' END transaction_type ,CASE tdt.database_transaction_state 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' END transaction_state FROM sys.dm_tran_database_transactions tdt INNER JOIN sys.dm_tran_session_transactions tst ON tst.transaction_id = tdt.transaction_id |
