Check if a transaction_id holds changes uncommitted-CodePudding

We have a problem of random sessions that leave their transactions open with changes uncommited and eventually blocking other users. Is it possible to query the server metadata to check if a certain transaction_id holds changes uncommitted ? (our ERP is written so it permanently has a transaction open, but it always should commit any change done as soon as possible).

I want to write a module on the ERP to log changes uncommited when none should exist (closing windows, etc. ...), to detect those bugs as soon as possible.

CodePudding user response：

Try using this as a starting point:

select st.session_id,
    dt.database_id,
    dt.database_transaction_log_bytes_used,
    dt.database_transaction_log_bytes_reserved, 
    datediff(second, s.last_request_end_time, getdate())
from sys.dm_tran_session_transactions as st
join sys.dm_tran_database_transactions as dt
    on dt.transaction_id = st.transaction_id
join sys.dm_exec_sessions as s
    on s.session_id = st.session_id;

You'll probably want to put some sort of threshold around amount of log used/reserved and/or how long the session has been idle.