SQL Server searches for uncommitted transactions for a long time

Welcome to the Windows community forum and interact with 3 million technical staff. the transaction ID is known in the first query of dm_tran_session_transactions, so you can use sys. the code for dm_tran_active_transactions to learn more about the transaction itself is as follows: selecttransaction_begin_time, casetra

Welcome to the Windows community forum, and interact with 3 million technical staff> because it is also from sys. the transaction ID is known in the first query of dm_tran_session_transactions, so you can use sys. the code for dm_tran_active_transactions to learn more about the transaction itself is as follows: select transaction_begin_time, case tra

Welcome to the Windows community forum and interact with 3 million technicians>

Because the transaction ID is also known from the first query of sys. dm_tran_session_transactions, you can use sys. dm_tran_active_transactions to learn more about the transaction itself.

The Code is as follows:

Select transaction_begin_time,

Case transaction_type

When 1 then 'read/Write transaction'

When 2 then 'read-Only transaction'

When 3 then 'System transaction'

When 4 then 'distributed transaction'

End tran_Type,

Case transaction_state

When 0 then 'not been comoletely initaialiaed yun'

When 1 then 'initaialiaed but ha notstarted'

When 2 then 'active'

When 3 then 'ended (read-only transaction )'

When 4 then 'commit initiated for distributed transaction'

When 5 then 'transaction prepared and waiting resolution'

When 6 then 'commited'

When 7 then 'being rolled back'

When 0 then 'been rolled back'

End transaction_state

From

Sys. dm_tran_active_transactions

Where transaction_ID = 455520

The Code is as follows:

/* Result:

Transaction_begin_time tran_Type transaction_state

14:05:29. 170 Read/Write transaction active

*/

Summary: The general tips for using DMV to troubleshoot and investigate long-time active transactions are demonstrated here. The basic steps are as follows:

1. query sys. dm_tran_session_transactions to obtain the ing between the session ID and the transaction ID.

2. query sys. dm_exec_connections and sys. dm_exec_ SQL _text to find the latest commands executed by the Session (most_recent_ SQL _Handle column)

3. Finally, query sys. dm_tran_active_transactions to determine the time, type, and status of the transaction.

With this technique, you can go back to the application to find out the discarded transactions called (opened but never committed) and the inappropriate transactions that are running too long or unnecessary for the application.

[1] [2]