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:

SQL SERVER - Transaction Log Impact Detection Using DMV - dm_tran_database_transactions  transaction

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)

Previous Post
SQL SERVER – FIX – ERROR : Msg 3201, Level 16 Cannot open backup device . Operating system error 5(Access is denied.)
Next Post
SQL SERVER – Finding Location of Log File when Primary Datafile is Crashed

Related Posts

No results found.

7 Comments. Leave new

  • Pinal, you should to design SSRS Dashboards based on selects like this. It will be great toolset for tuning database =)

    Reply
  • Any quick way of figuring out when (date&time) the transaction began?

    Reply
  • Hi Mr. Dave,

    Can you tell me please

    how to put indexing on temp table ?

    Reply
  • Nakul Vachhrajani
    April 14, 2011 10:59 pm

    Hello!

    Just a thought – does this mean that Microsoft SQL Server can have a maximum of 1000 internal transactions running at any given time?

    Thanks & Regards,
    Nakul Vachhrajani

    Reply
  • Hello Pinal, recently asked a question on stackexchange about following the progress of a rollback. Max Vernon suggested that dm_tran_database_transactions could be used. Have you ever tried to follow the progress of a large rollback and if so, what did you do?

    Reply

Leave a Reply

Menu