3

fn_dblog() shows transaction IDs in this format:

0000:00049d43

And sys.dm_tran_database_transactions shows transaction IDs in this format:

9811233

I believe the above examples show the same transaction ID in different formats.

Is there a way to convert between the two formats? Or are these actually two different entities -- and in that case, is there a way of matching up fn_dblog() with the transaction-related DMVs?

4

I do not believe that the [Transaction ID] column of sys.fn_dblog() is the transaction ID you are looking for. The transaction id in that result set that matches the transaction_id column of sys.dm_tran_database_transactions is [Xact ID].

Try the following:

Find a Transaction ID

SELECT *
FROM   sys.dm_tran_database_transactions
WHERE  database_id = DB_ID();

Get that Transaction's details from the Log

DECLARE @TransactionID NVARCHAR(50);

SELECT @TransactionID = [Transaction ID]
FROM   sys.fn_dblog(NULL, NULL)
WHERE  [Xact ID] = 10450416; -- some [transaction_id] from sys.dm_tran_database_transactions

--SELECT @TransactionID;

SELECT *
FROM   sys.fn_dblog(NULL, NULL)
WHERE  [Transaction ID] = @TransactionID;
1
  • 1
    Thank you! Hopefully this helps someone else who has the same misconception that I had. Dec 15 '16 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.