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:
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.