How do I convert between the transaction ID formats in fn_dblog() and sys.dm_tran_database_transactions?

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?

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;

StackExchange Database Administrators Q#157869, answer score: 5