How to find the modified delta value for an update transaction after a rollback in a transaction log in SQL Server

Question

I am trying to understand the internals of the tranaction mssql log. I am doing the following transaction

Begin TRANSACTION update xyz1 set a=6 Rollback TRANSACTION 

Initially, the value of column 'a' is 5. Thus, the result of the transaction was found as shown below, using

 SELECT Operation, [RowLog Contents 0], [RowLog Contents 1, AllocUnitName, [Page ID], [Slot ID], [Offset in Row],[Transaction ID] FROM sys.fn_dblog(NULL,NULL) operation rowcontent0 row content 1 AllocUnitName Page ID Slot ID Offset LOP_BEGIN_XACT NULL NULL NULL NULL NULL LOP_MODIFY_ROW 0x01 0x06 dbo.xyz1 0001:0000022e 0 4 LOP_MODIFY_ROW 0x 0x01 dbo.xyz1 0001:0000022e 0 4 LOP_ABORT_XACT NULL NULL NULL NULL NULL 

As you can see from the above values, the data changes from 1 to 6, and then again changes to 1, because there was a rollback.

Then, to get the memory information, I use the following command: 558 (22e) is the page identifier that can be found in the above table

 dbcc traceon(3604) dbcc page(lumrecon,1,558,3) 

go to slot 0 and rowoffset 4 to see the changed data

 00000000: 10000800 01000000 010000†††††††††††††........... 

We observe from the memory dump that we find only the value of the rollback transaction, i.e. a = 1.

But my question is how to find the initial value of ie 6 in the transaction that occurred. Is this Microsoft behavior that doesn't intentionally write information to the log or is there any way? please suggest.

Thank you in advance

Answer 1

"0x06" in the log line

 LOP_MODIFY_ROW 0x01 0x06 dbo.xyz1 

is the new value written to the column. Since the transaction is rolled back, information about "6" is now only in the log, and not in the page (memory) buffer. In memory, there is always the last value for a row (including incomplete transactions), potentially protected by logical locking, and sometimes by a latch.