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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

SELECT transaction_id, DB_NAME(database_id) AS DBName, database_transaction_begin_time, CASE database_transaction_type WHEN 1 THEN 'Read/Write' WHEN 2 THEN 'Read-Only' WHEN 3 THEN 'System' ELSE 'Unknown Type - ' + convert(VARCHAR(50), database_transaction_type) END AS TranType, CASE database_transaction_state WHEN 1 THEN 'Uninitialized' WHEN 3 THEN 'Not Started' WHEN 4 THEN 'Active' WHEN 5 THEN 'Prepared' WHEN 10 THEN 'Committed' WHEN 11 THEN 'Rolled Back' WHEN 12 THEN 'Comitting' ELSE 'Unknown State - ' + convert(VARCHAR(50), database_transaction_state) END AS TranState, database_transaction_log_record_count AS LogRecords, database_transaction_replicate_record_count AS ReplLogRcrds, database_transaction_log_bytes_reserved/1024.0 AS LogResrvdKB, database_transaction_log_bytes_used/1024.0 AS LogUsedKB, database_transaction_log_bytes_reserved_system/1024.0 AS SysLogResrvdKB, database_transaction_log_bytes_used_system/1024.0 AS SysLogUsedKB FROM sys.dm_tran_database_transactions WHERE database_id NOT IN (1, 2, 3, 4, 32767)

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.

reference: http://www.sqlservergeeks.com/sys-dm_tran_database_transactions/