Active Transactions - How long, Current State, Bytes Reserved/Used, Which DB and What Query ?

CASE dbt.database_id WHEN 32767 THEN 'Resource' ELSE END [DB],
a.session_id [Session],
a.open_transaction_count [Tran. Count],
b.transaction_begin_time [Tran. Started at],
DATEDIFF(SECOND,b.transaction_begin_time,GETDATE()) [Tran. Active(Second(s))],
CHOOSE(b.transaction_type,'Read/write transaction','Read-only transaction','System transaction','Distributed transaction') [Tran. Type],
CHOOSE(b.transaction_state,'The transaction has been initialized but has not started.','The transaction is active.','The transaction has ended. This is used for read-only transactions.','The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.','The transaction is in a prepared state and waiting resolution.','The transaction has been committed.','The transaction is being rolled back.','The transaction has been rolled back.') [Transaction Sate],
dbt.database_transaction_log_bytes_reserved [Log reserved (Bytes)],
dbt.database_transaction_log_bytes_used [Log used (Bytes)],
x.[text] [Actual Query]
FROM sys.dm_tran_session_transactions a JOIN sys.dm_tran_active_transactions b
ON (a.transaction_id = b.transaction_id)
JOIN sys.dm_exec_connections C ON (a.session_id = C.session_id)
JOIN sys.dm_tran_database_transactions DBT ON(dbt.transaction_id = b.transaction_id)
LEFT JOIN sys.Databases D ON (D.database_id = DBT.database_id) 
CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) x 

No comments:

Post a Comment