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 (http://blog.SQLAuthority.com)

9 thoughts on “SQL SERVER – Transaction Log Impact Detection Using DMV – dm_tran_database_transactions

  1. Pingback: SQL SERVER – A Quick Look at Logging and Ideas around Logging « SQL Server Journey with SQL Authority

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #025 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s