In-depth parsing of SQL Server row migration records

This document describes how to store cross-page records in a database based on the storage structure of mdf data files in the SQLServer database. This article is the internal research result of the North Asia data recovery center. Currently, it is only published on 51CTO.

This document describes how to store cross-page records in a database based on the storage structure of mdf data files in the SQLServer database. This article is the internal research result of the North Asia data recovery center. Currently, it is only published on 51CTO.

This document describes the storage methods for cross-page records in the database based on the mdf data file storage structure of the SQL Server database. This article is the internal research result of the North Asia data recovery center. Currently, it is only published on 51CTO. Please contact the author when reprinting the article.

40002000200020002000

78

78401780341

)

M_slottery CNT = 1 m_freeCnt = 3065 m_freeData = 5125

Slot 0 Offset 0x60 Length5029

Record Type = FORWARDED_RECORD RecordAttributes = NULL_BITMAPVARIABLE_COLUMNS

Redis Size = 5029

Memory Dump @ 0x000000000D30A060

0000000000000000: 3200080001000000 03000003 00cb0b9b 2 ...............

0000000000000010: 13a5921362626262 62626262 62626262... bbbbbbbbbbbbbbb

0000000000000020: 62626262 62626262 62626262 62626262 bbbbbbbbbbbbbbbbbb

0000000000000030: 62626262 62626262 62626262 62626262 bbbbbbbbbbbbbbbbbb

......

On the 80th page, there is a record slot 0, starting from 96 bytes, length is 5029, record type is forwarded_record. this record is the overflow record pointed to in forwarding_stub, recording the actual data of slot0 data record on page 1.

This is a complete record, slightly different from the general record format. The biggest difference is that a column of variable-length data is added to the record. This variable-length column is added at the end of the record. It is only used by the storage engine and transparent to the upper layer. The column information recorded also changes.

Add 1 to the variable-length columns in the column information, but the total number of columns does not change. An extra item in the Variable Length Column offset array is used to specify the end offset of the added data column. The length of the Variable Length Column is actually fixed and is 10 bytes, this data should be placed at the end of the record considering the access performance. This column of data is not actually a standard column of data, it should be a structure data of forwarding_stub. A rowid pointer is also stored in it, and the position pointer pointing to the record before removal is saved in 8 bytes.

The column data to be added has some special features. The end offset of column data in the column information is non-conventional and stored in the overflow format. The value is greater than 8192 bytes. In fact, the actual value is 0x8000.

The preceding forwarded record is explained as follows:

The value of status A in the record header is 0x32, which is interpreted as A forwarded removal record with A variable length column and A null bitmap. The record length is 5029 bytes. The column information is 03000003 00cb0b9b13a593. It can be parsed Based on the column information structure. The total number of columns is 3, which contains three fields: a, B, and c. The bitmap value of Null is 0, indicating that no column is empty. The number of variable-length columns is also 3, which includes variable-length columns B, c, and add columns. There are three end offset arrays in the back, occupying 6 bytes, indicating the end position of the three variable-length columns respectively. The first two are easy to understand. The third is 0x93A5, which is converted into a decimal value of 37797. This is obviously abnormal. The actual offset value should be 0x8000, that is, 0x13A5, convert to decimal 5029. It is no coincidence that this value has been seen at the total length of the previous record.

In this way, you can manually retrieve the value of each field. Retrieve the value of the last column and you will find it special:

0004 4e0000000000000. The data length is 10 bytes, which is similar to forwarding. It only adds a byte 00 to the header. The rowid in the field records the address of the original forwarding record. Here is (1: 78: 0). This address Pointer Points to the previous forwarding record.

The following figure summarizes the overall structure of forwarding and forwarded records described in this section.

Figure connecting Forwarding records and forwarded records

Is parsed in winhex: