How to Read Data Updates in Log File (Part 1)

In the first part of this topic, let’s use this sample table ‘TestTbl’ and fields with fixed length for the explanation of reading data updates in Microsoft SQL Server 2000’s log file (LDF file) :

du11

And I Initiate the data as follows :

du12

The field description and location is described in sysobjects and syscolumns table in the current database (each database has sysobjects and syscolumns table) :

SELECT *
FROM sysobjects
WHERE (name = ‘TestTbl’)

du13

The id will be used to query the syscolumns for information of the offset location of each field :

SELECT     name, length, colid, xoffset
FROM         syscolumns
WHERE     (id = 357576312)
ORDER BY name, colid

du14

Now, if I change the consecutive field (for example : fielda and fieldb) :

du15

The log will look like this :

du16

Element 0 is the old value data starting from offset 4, and the affected content has length 18. This means that the fielda at offset 4 is changed, and because the fixed length of fielda is 10 and the content length is 18, then this also affected fieldb at offset 14.

Element 1 is the new value, also starting from offset 4.

The content of row data can be viewed with Hex Editor program such as WinHex using “ASCII Hex Clipboard Format”.

This is the dump of row data for element 0 (Old Value) in WinHex :

du17

This is the dump of row data for element 1 (New Value) in WinHex :

du18

The conclusion is, as long as the change is affected for consecutive fields, the SQL Server will always create 2 log records. One for the old value, second for the new value. So, if I change the data in fielda, fieldb, fieldc, the SQL server will also creates 2 log records. Or If I change the data in fieldb and fieldc, SQL server will also creates 2 log records.

Now, what happens if I change the field not in consecutive order ? Let’s change fielda and fieldc and see what happened :

du19

Instead of just 2 elements, now, in the log file SQL Server creates 7 elements.

Element 0 contains lists affected offset, both for old value and new value. Since we are using the fixed length, the offset for old value is always the same with offset for new value.

In this sample, the update is perform at offset 0x04 (big endian format should be transposed first) and 0x29. From syscolumns information, we can deduce that offset 0x04 is fielda and 0x29 = 41 is within the range of fieldc (between offset 0x22 and 0x39).

Element 1 denotes the length list of updated data. In this sample :

0x0800 0100

The big endian should be transposed : 0x0800 to 0x0008, 0x0100 = 0x0001

The rest of the element is the list of old and new values respectively (i.e element 2 is old value for first offset, element 3 is new value for first offset, etc).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: