Anatomy of Log Record (Part 1)

Let’s take some random log record from Microsoft SQL Server 2000’s LDF file to be examined :

lr1

You can refer to my previous post about determining the exact position of log record of interest.

In this anatomy autopsy session :), the focus is to determine the operation, the page id and the slot id of affected database.

Offset 0x0E = 0x06 is the log operation which means (LOP_MODIFY_COLUMNS). You can refer to the complete list of log operation at the end of this post.

Offset 0x0F = 0x01 is the log operation context (LCX_HEAP). The complete list is at the end of this post.

Offset 0x18 through 0x1D is Page Id in the format fileid:page number. In this sample, page id is :
0001:00001F74 which is m_pageId = 1:8052.

Offset 0x1E to 0x1F denotes slot id. In this case, the updated slot is 0x0026 = 38

Below is the list of all log operation codes and its description :

0x00 = LOP_NULL
0x01 = LOP_FORMAT_PAGE
0x02 = LOP_INSERT_ROWS
0x03 = LOP_DELETE_ROWS
0x04 = LOP_MODIFY_ROW
0x05 = LOP_MODIFY_HEADER
0x06 = LOP_MODIFY_COLUMNS
0x07 = LOP_SET_BITS
0x08 = LOP_SET_MASK_BITS
0x09 = LOP_DELTA_SYSIND
0x0A = LOP_SET_FREE_SPACE
0x0B = LOP_DELETE_SPLIT
0x0C = LOP_UNDO_DELETE_SPLIT
0x0D = LOP_EXPUNGE_ROWS
0x10 = LOP_FILE_HDR_MODIFY
0x11 = LOP_CLEAR_GAM_BITS
0x80 = LOP_BEGIN_XACT
0x81 = LOP_COMMIT_XACT
0x82 = LOP_ABORT_XACT
0x83 = LOP_PREP_XACT
0x84 = LOP_MARK_SAVEPOINT
0x85 = LOP_FORGET_XACT
0x88 = LOP_MARK_DDL
0x86 = LOP_CREATE_FILE
0x87 = LOP_DROP_FILE
0x96 = LOP_BEGIN_CKPT
0x98 = LOP_XACT_CKPT
0x99 = LOP_END_CKPT
0x9A = LOP_BUF_WRITE
0x9B = LOP_IDENTITY_TYPE
0xA0 = LOP_BEGIN_RECOVERY
0xA1 = LOP_END_RECOVERY
0xA2 = LOP_NONLOGGED_OP
0xAA = LOP_SORT_BEGIN
0xAC = LOP_SORT_EXTENT
0xAB = LOP_SORT_END
0xAD = LOP_CREATE_INDEX
0xAE = LOP_DROP_INDEX
0xAF = LOP_SORT_MEMORY
0xC8 = LOP_REPL_COMMAND
0xC9 = LOP_BEGIN_UPDATE
0xCA = LOP_END_UPDATE
0xCB = LOP_TEXT_POINTER
0xCC = LOP_TEXT_INFO_BEGIN
0xCD = LOP_TEXT_INFO_END
0xCE = LOP_REPL_NOOP
0xCF = LOP_TEXT_VALUE
0xD3 = LOP_SHRINK_NOOP

List of all log operation context :

0x00 = LCX_NULL
0x01 = LCX_HEAP
0x02 = LCX_CLUSTERED
0x03 = LCX_INDEX_LEAF
0x04 = LCX_INDEX_INTERIOR
0x05 = LCX_TEXT_MIX
0x06 = LCX_TEXT_TREE
0x07 = LCX_DISTRIBUTION
0x08 = LCX_GAM
0x09 = LCX_SGAM
0x0A = LCX_IAM
0x0B = LCX_PFS
0x0C = LCX_IDENTITY_VALUE
0x0D = LCX_OBJECT_ID
0x0E = LCX_NONSYS_SPLIT
0x0F = LCX_CLUSTERED_BULK
0x10 = LCX_INDEX_LEAF_BULK
0x11 = LCX_FILE_HEADER
0x12 = LCX_SCHEMA_VERSION
0x13 = LCX_MARK_AS_GHOST
0x14 = LCX_BOOT_PAGE
0x15 = LCX_SYSCONFIG_PAGE
0x16 = LCX_SORT_MIX
0x17 = LCX_BOOT_PAGE_CKPT
0x18 = LCX_DIFF_MAP
0x19 = LCX_ML_MAP

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: