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,
WHEN 1 THEN 'Read/Write'
WHEN 2 THEN 'Read only'
WHEN 3 THEN 'System' END AS TransactionType,
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,
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.
SET ModifiedDate = ModifiedDate+1
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 (http://blog.SQLAuthority.com)