Joining sys.dm_tran_database_transactions to fn_dblog Results
In my previous two posts, Reading the Transaction Log Using fn_dblog and Using dm_tran_database_transactions and a First Step to Converting LSNs, I demonstrated using these DMOs to see what transactions are executing and to read the transaction log. In this post, I'll show how to join those results together. Here is the script I used:
USE [Your Database Name Here] GO -- Declare variables DECLARE @startlsn VARBINARY(15) DECLARE @lastlsn VARBINARY(15) DECLARE @LSN_START NVARCHAR(50) DECLARE @LSN_END NVARCHAR(50) DECLARE @dbname NVARCHAR(100) DECLARE @bytesused BIGINT DECLARE @bytesreserved BIGINT DECLARE @loopcount INT DECLARE @looplimit INT -- Initialize the loop counter SET @loopcount = 1 -- Create a temp table to hold all of the active transactions from -- sys.dm_tran_database_transactions CREATE TABLE #tranresults (dbname NVARCHAR(100) ,bytesused BIGINT ,bytesreserved BIGINT ,startlsn VARBINARY(15) ,endlsn VARBINARY(15)) -- Create a temp table to hold data about each transaction and the associated log records CREATE TABLE #logresults (dbname NVARCHAR(100) ,bytesused BIGINT ,bytesreserved BIGINT ,spid INT ,begintime NVARCHAR(48) ,currentlsn VARCHAR(46) ,previouslsn VARCHAR(46) ,transactionid VARCHAR(28) ,operation NVARCHAR(62) ,context VARCHAR(62) ,logreserve INT ,allocunitname NVARCHAR(774) ,pageid NVARCHAR(28) ,numberoflocks SMALLINT ,lockinformation NVARCHAR(512)) -- Insert data from sys.dm_tran_database_transaction for all transactions that have -- generated log records -- Notice the use of the fn_convertnumericlsntobinary function -- This is necessary for subsequent SUBSTRING operations INSERT INTO #tranresults SELECT DB_NAME(database_id), database_transaction_log_bytes_used, database_transaction_log_bytes_reserved, master.dbo.fn_convertnumericlsntobinary(database_transaction_begin_lsn), master.dbo.fn_convertnumericlsntobinary(database_transaction_last_lsn) FROM sys.dm_tran_database_transactions WHERE database_transaction_begin_lsn IS NOT NULL AND database_transaction_last_lsn IS NOT NULL AND database_transaction_state = 4 AND database_id = DB_ID() -- Set the @looplimit variable to the number of rows selected from -- sys.dm_tran_database_transactions SET @looplimit = @@ROWCOUNT -- Run through this loop once for each row selected from -- sys.dm_tran_database_transactions WHILE @loopcount <= @looplimit BEGIN -- Select data from the first row into variables for later use SELECT TOP 1 @dbname = dbname, @bytesused = bytesused, @bytesreserved = bytesreserved, @startlsn = startlsn, @lastlsn = endlsn FROM #tranresults -- Convert the starting and ending lsn for use in the fn_dblog function below -- The following code is necessary because fn_dblog expects the lsn as colon-delimited -- numeric values, represented as VARCHAR -- I initially thought I could get away with converting the numeric lsn to VARCHAR and -- running the SUBSTRING operations on it, but the numeric value is variable in length SET @LSN_START = CONVERT(VARCHAR(10),CONVERT(INT,SUBSTRING(@startlsn,1,4))) + ':' + CONVERT(VARCHAR(10),CONVERT(INT,SUBSTRING(@startlsn,5,4))) + ':' + CONVERT(VARCHAR(10),CONVERT(INT,SUBSTRING(@startlsn,9,2))) SET @LSN_END = CONVERT(VARCHAR(10),CONVERT(INT,SUBSTRING(@lastlsn,1,4))) + ':' + CONVERT(VARCHAR(10),CONVERT(INT,SUBSTRING(@lastlsn,5,4))) + ':' + CONVERT(VARCHAR(10),CONVERT(INT,SUBSTRING(@lastlsn,9,2))) SELECT @LSN_START SELECT @LSN_END -- Now having re-reconverted the lsns, exit our hero, stage left -- For transactions with a non-null start and end lsn, insert required data into -- the #logresults temp table IF (@LSN_START IS NOT NULL) AND (@LSN_END IS NOT NULL) BEGIN INSERT INTO #logresults SELECT @dbname AS [Database], @bytesused AS [Bytes Used], @bytesreserved AS [Bytes Reserved], SPID, [Begin Time], [Current LSN], [Previous LSN], [Transaction ID], Operation, Context, [Log Reserve], AllocUnitName, [Page ID], [Number of Locks], [Lock Information] FROM ::fn_dblog(@LSN_START, @LSN_END) END -- Delete the top row from the #tranresults temp table DELETE TOP (1) FROM #tranresults SET @loopcount = @loopcount + 1 END SELECT * FROM #logresults ORDER BY currentlsn -- Clean up the temp tables DROP TABLE #logresults DROP TABLE #tranresults
The comments do a good job of explaining what the script is doing, but I want to clarify a few things. Because I was unable to join the DMOs on transaction ID, I decided to use the begin and end lsn values from the DMV as input parameters to fn_dblog. And here's where the fun began. The DMV stores the lsns as numeric values. The function expects a colon-delimited VARCHAR string made up of numbers. For example, if the DMV stores an lsn value of 4242000000012300002, fn_dblog will expect 4242:123:2.
To do the the conversion, I followed these steps:
- Converted the values to hex using fn_convertnumericlsntobinary;
- Substringed each of the colon-delimited parts of the lsn, converted to INT;
- Converted the INT values to VARCHAR;
- Concatenated the VARCHAR values with colons.
And so, having re-reconverted the lsns, our hero is ready to pass them into fn_dblog. The results of fn_dblog are inserted to the temp table for each set of lsns, which is selected from at the end of the script.
A few points about the result set: fn_dblog is database-specific and the result set only contains log records for currently active transactions. Because the tran log acts like a stream of consciousness for the database, you may see additional LOP_BEGIN_XACT records following the LOP_BEGINXACT for the first transaction returned. Here is an example showing a transaction beginning and committing (highlighted in yellow) while another transaction is open. Notice that the currentlsn values are sequential while the previouslsn values are grouped by transactionid.
I hope this series of posts provides insight into the transaction log and how to relate it to active transactions on an instance.