Anatomy of Log Record (Part 2)

In this second part of Log Record dissection, let’s using another sample log record from sample Microsoft SQL Server 2000’s LDF file :

lr21

The log operation code at offset 0x0E is 0x04 which is LOP_MODIFY_ROW.

I will describe the data format of this log operation code as follows :

The emphasis is try to locate the bits for Offset in Row, Num Elements (Number of Element Records), Element Length and Row Data Location.

This data is important if we try to reconstruct the update operations on certain table, which is also available in this blog.

Offset in Row bit value is located at offset 0x30
Total Number of Element (Num Elements) is at offset 0x34 to 0x35 in big endian format.
From offset 0x36 onward is repeated data of element length, ends with 2 bytes of 0x00, followed by the actual Row Data.

Parts of each row data is determined by Element Length of each element record. For the ease of calculation, let’s move the row data to the new file using hex editor :

lr22

First Row Data has the length 0x10 = 16, so the first row data starts at offset 0x00 to 0xFF
0x67666C64612020206D794669656C6462

Second Row Data has the length 0x10 = 16, so the second row starts at offset 0x10 to 0x1F
0x61676161202020206368616762622020

In the next part, I will deal with log operation LOP_MODIFY_COLUMNS. You may ask, why I painstakingly decipher this log record manually. This is because in certain cases, the log record is out there in the log file but for reasons still unknown, DBCC LOG refuses to decipher it for us.

This two kind of log operation is important because it is closely linked with user’s or application’s data.

There are other techniques (in other post) to view any log record of interest, i.e. by performing the cut and paste of log block, using hex editor from the log block in the source file and log sector location of the destination file.

The above techniques is used when even using cut-and-paste method failed.

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: