sys.dm_tran_database_transactions – Day 48 – One DMV a Day

Hello Geeks and welcome to the Day 48 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Yesterday I have started transaction related DMVs. I have covered sys.dm_tran_locks. Today I will be covering sys.dm_tran_database_transactions. This DMV gives you information like the log usage by each transaction in a database.

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.

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.

Once covering all DMVs in the series I will be blogging on different combination of these DMVs. Tomorrow I will be covering one more DMV related to transactions. So stay tuned. Till then.

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook