SQL SERVER – Transaction Log Impact Detection Using DMV – dm_tran_database_transactions

Just a few days ago before I received the email from blog reader asking if there is any DMV which can provide details about the effect of a transaction on the transaction log file. Absolutely! Here is a quick script which can provide the necessary details:

SELECT transaction_id, DB_NAME(database_id) DatabaseName,
database_transaction_begin_time TransactionBegin,
CASE database_transaction_type
WHEN 1 THEN 'Read/Write'
WHEN 2 THEN 'Read only'
WHEN 3 THEN 'System' END AS TransactionType,
CASE database_transaction_state
WHEN 1 THEN 'Not Initialized'
WHEN 3 THEN 'Transaction No Log'
WHEN 4 THEN 'Transaction with Log'
WHEN 5 THEN 'Transaction Prepared'
WHEN 10 THEN 'Commited'
WHEN 11 THEN 'Rolled Back'
WHEN 12 THEN 'Commited and Log Generated' END AS TransactionState,
database_transaction_log_record_count LogRecordCount,
database_transaction_log_bytes_used LogBytesUsed,
database_transaction_log_bytes_reserved LogBytesReserved
FROM sys.dm_tran_database_transactions
WHERE transaction_id > 1000


All the transaction_ID which are over 1000 are user transactions. Let us test the above query to run following transaction.

BEGIN TRANSACTION
UPDATE HumanResources.Department
SET ModifiedDate = ModifiedDate+1
--ROLLBACK


If you do not run ROLLBACK, it will not be a completed transaction. When you run the DMV script that’s listed above, it will give you the following output:

You can clearly see many information over here- from Transaction start time to its types, as well as how many bytes are used and reserved.

Reference: Pinal Dave (https://blog.sqlauthority.com)