Home > DMO, Immersion Events, SQL Server, SQL Server Transaction Log, T-SQL > Using dm_tran_database_transactions and a First Step to Converting LSNs

Using dm_tran_database_transactions and a First Step to Converting LSNs

In my last post, I showed how to use fn_dblog to read the transaction log. In this post, I will demonstrate using sys.dm_tran_database_transactions DMO to get information about executing transactions. You can find the complete result set for the DMO at this Books Online entry. For this post, I am interested in the following results:

SELECT DB_NAME(database_id),                                                -- Return the database name
database_transaction_log_bytes_used,                                        -- Log bytes used
database_transaction_log_bytes_reserved,                                    -- Log bytes reserved
master.dbo.fn_convertnumericlsntobinary(database_transaction_begin_lsn),    -- Beginning lsn of the transaction
master.dbo.fn_convertnumericlsntobinary(database_transaction_last_lsn)      -- Last lsn of the transaction
FROM sys.dm_tran_database_transactions 
WHERE database_transaction_begin_lsn IS NOT NULL                            -- Confirm that the transaction has 
AND database_transaction_last_lsn IS NOT NULL                               -- a start and end lsn
AND database_transaction_state = 4                                          -- Indicates a transaction that has written to the transaction log
AND database_id = DB_ID()                                                   -- Return rows for the current database

The results look like this:

dm_tran_database_transactions results


You will notice that the lsns are being converted using fn_convertnumericlsntobinary. The code for this function is available here at step 3 of alternative approach 1. This is the first in a magical, multi-step odyssey to join the results of sys.dm_tran_database_transactions to fn_dblog.

My initial attempt was a straight join between the function and the DMO on transaction_id, which exists in both. I ran an explicit transaction without a commit, and compared the transaction_ids. The DMO returned the value 1200932 while the function returned 0005602A, the first decimal and the second hex. No big deal, just convert one of them. However, 1200932 converts to 125324 in hex. So, I was stumped but tried to make sense of it. Do I double it and add 30? After spending too much time trying to figure it out, I wrote Paul Randal (b|t) and explained my problem. His response? "The transaction IDs don't match - annoyingly."

So, with that bit of wisdom I set out to try another method. This involved using the lsn information from the DMO as input parameters to fn_dblog. My next post will show you more than you ever wanted to know about converting lsns.

Leave a Reply

%d bloggers like this: