Tuesday, July 10, 2018

sys.dm_tran_database_transactions

Sys.dm_tran_database_transactions can be joined with sys.dm_tran_session_transactions or sys.dm_tran_locks on transaction_id. From there we can use the session_id to join with sys.dm_exec_sessions or sys.dm_exec_requests. This will get more information like statement, execution details, etc.
In yesterday’s post I have run a transaction to demonstrate the usage of sys.dm_tran_locks. Today I will use the same transaction and demonstrate how to use sys.dm_tran_database_transactions.
sys.dm_tran_database_transactions
You will see the transaction type and state from sys.dm_tran_database_transactions. Also from the above output you can have
database_transaction_log_record_count – Number of log records for the transaction
database_transaction_replicate_record_count – Number of log records that will be replicated
database_transaction_log_bytes_reserved – Log space reserved by the transaction
database_transaction_log_bytes_used – Log space used by the transaction
database_transaction_log_bytes_reserved_system – Log space reserved by system on behalf of the transaction
database_transaction_log_bytes_used_system – Log space used by system on behalf of the transaction
So you can check from sys.dm_tran_database_transactions how much log is used. This will help you in checking what are highest log consumers in your transaction log file.

No comments: