Execute the following SQL statement first, my test environment is SQL2005
DBCC TRACEON (3604) go
DBCC page (master,1,0,2)
You can see some of the physical structure information for the MDF file, which includes the important first 96 bytes. That is, the first page of the file header.
........
PAGE HEADER:
Page @0x03fa0000
M_pageid = (1:0) m_headerversion = 1 M_type = 15
M_typeflagbits = 0x0 M_level = 0 M_flagbits = 0x8
M_objid (allocunitid.idobj) = M_indexid (Allocunitid.idind) = 0 Metadata:allocunitid = 6488064
Metadata:partitionid = 0 Metadata:indexid = 0 Metadata:objectid = 99
M_prevpage = (0:0) M_nextpage = (0:0) Pminlen = 0
m_slotcnt = 1 m_freecnt = 7937 M_freedata = 3059
m_reservedcnt = 0 M_lsn = (149:448:1) m_xactreserved = 0
M_xdesid = (0:0) m_ghostreccnt = 0 M_tornbits = 1073741694
........
DATA:
Memory Dump @0x62fec000
62fec000:010f0000 08000000 00000000 00000000† ....... .....
62fec010:00000000 00000100 63000000 011ff30b†........c ....
62fec020:00000000 01000000 95000000 c0010000† ....... .....
62fec030:01000000 00000000 00000000 820000c0† ....... .....
62fec040:00000000 00000000 00000000 00000000† ....... .....
62fec050:00000000 00000000 00000000 00000000† ....... .....
The above blue text is the file header of some information. If this information is damaged, it will have serious consequences.
After a simple byte-by-bit analysis, the bitconverter.getbytes functions of Windows Calculator and C # are used in the middle . The following file structure is obtained, where each line is 4 bytes, and the first 64 bytes of the file header are analyzed in total.
| 00:0f |
M_headerversion |
M_type |
M_typeflagbits |
M_level |
| M_flagbits |
M_indexid |
| M_prevpage (2) |
| M_prevpage (1) |
Pminlen |
| 10:1f |
M_nextpage (2) |
| M_nextpage (1) |
M_slotcnt |
| Allocunitid.idobj |
| M_freecnt |
M_freedata |
| 20:2f |
M_pageid (2) |
| M_pageid (1) |
M_reservedcnt |
| M_LSN (1) |
| M_LSN (2) |
| 30:3f |
M_LSN (3) |
M_xactreserved |
| M_xdesid (2) |
| M_xdesid (1) |
M_ghostreccnt |
| M_tornbits |
In the first 96 bytes of the database, 0x40 starts straight 0x5f should be 0.
I found that only the number in front of the M_pageid Colon of the test page was not 1 o'clock to write the data in 0x40 to 0x5f. But what the concrete representative has not yet seen.
Think of the first page of the database 0x00-0x3f as shown, 0x40-0x5f are 0 (incorrect, please correct it)
What's the use of this picture, if you understand the meaning of the above parameter, it is possible to restore the corrupted database by opening a damaged MDF file with the binary editor.
I am not a DBA or a professional recovery data, just a common developer, how to recover also ask an experienced person to add.
Sentient reminds, these things are very dangerous, please do not feel free to test, it is best to find a useless database to study.
The meaning of the parameter
M_pageid
This identifies the file number, the page is part of and the position within the file. (1:143) means page 143 in file 1.
M_headerversion
This is the page header version. Since version 7.0 This value have always been 1.
M_typea
This is the page type. The values of your ' re likely to see is:
1-data page. This holds the data records in a heap or clustered index leaf-level.
2-index page. This holds index records in the upper levels of a clustered index and all levels of non-clustered indexes.
3-text Mix page. A Text page that holds small chunks of LOB values plus internal parts of text tree. These can is shared between LOB values in the same partition of an index or heap.
4-text Tree page. A Text page that holds large chunks of the LOB values from a single column value.
7-sort page. A page that stores intermediate results during a sort operation.
8-gam page. Holds global allocation information about extents in a GAM interval (every data file was split into 4GB chunks-the number of extents that can is represented in a bitmap to a single database page). Basically whether an extent are allocated or not. GAM = Global Allocation Map. The first one is page 2 in each file. More on these in a later post.
9-sgam page. Holds global allocation information about extents in a GAM interval. Basically whether an extent are available for allocating mixed-pages. SGAM = Shared GAM. The first one is Page 3 in each file. More on these in a later post.
10-iam page. Holds allocation information about which extents within a GAM interval is allocated to an index or allocation unit, in SQ L Server and 2005 respectively. IAM = Index Allocation Map. More on these in a later post.
11-pfs page. Holds allocation and free space information about pages within a PFS interval (every data file was also split into approx 6 4MB chunks-the number of pages that can be represented in a byte-map on a single database page. PFS = Page free Space. The first one is page 1 in each file. More on these in a later post.
13-boot page. Holds information about the database. There ' s only one of these in the database. It ' s page 9 in file 1.
15-file Header page. Holds information about the file. There ' s one per file and it's page 0 in the file.
16-diff Map page. Holds information about which extents in a GAM interval has changed since the last full or differential backup. The first one is page 6 in each file.
17-ML Map page. Holds information about which extents in a GAM interval has changed while in bulk-logged mode since the last backup. This is, what allows, bulk-logged mode for bulk-loads, and index rebuilds without worrying about breaking a Backup chain. The first one is page 7 in each file.
M_typeflagbits
This is mostly unused. For data and index pages it'll always be 4. For all other pages it'll always be 0-except PFS pages. If a PFS page has m_typeflagbits of 1, which means that at least one of the pages in the PFS interval mapped by the PFS Pag e have at least one ghost record.
M_level
The the level and the page are part of of the B-tree.
Levels is numbered from 0 on the leaf-level and increase to the single-page root level (i.e. the top of the B-tree).
In SQL Server, the leaf level of a clustered index (with data pages) is level 0, and the next level up (with index p Ages) was also level 0. The level then increased to the root. So to determine whether a page is truly at the leaf level in SQL Server #, you need to look at the M_type as well as T He m_level.
For all page types apart from index pages, the level is always 0.
M_flagbits
This stores a number of different flags that describe the page. For example, 0x200 means the page have a page checksum on it (as we example page does) and 0x100 means the page has T Orn-page Protection on it.
Some bits is no longer used in SQL Server 2005.
M_objid
M_indexid
In SQL Server A, these identified the actual relational object and index IDs to which the page is allocated. In SQL Server 2005 This is no longer the case. The allocation metadata totally changed so these instead identify what's called the allocation unit that the page belongs to (I-ll do another post, that describes these later today).
M_prevpage
M_nextpage
These is pointers to the previous and next pages at the A level of the B-tree and store 6-byte page IDs.
The pages in each level of an index is joined in a doubly-linked list according to the logical order (as defined by the I Ndex keys) of the index. The pointers does not necessarily point to the immediately adjacent physical pages in the file (because of fragmentation).
The pages on the left-hand side of a b-tree level would have the M_prevpage pointer is NULL, and those on the right-hand SI De would has the m_nextpage be NULL.
In a heap, or if an index is only have a single page, these pointers would both be NULL for all pages.
Pminlen
This was the size of the fixed-length portion of the records on the page.
M_slotcnt
This is the count of records on the page.
M_freecnt
This is the number of bytes of free space in the page.
M_freedata
The offset from the start of the page to the first byte after the end of the last record on the page. It doesn ' t matter if there is a free space nearer to the start of the page.
M_reservedcnt
This is the number of bytes in free space which has been reserved by active transactions, that freed up space on the page. It prevents the free space from being used up and allows the transactions to roll-back correctly. There ' s a very complicated algorithm for changing this value.
M_lsn
The the log Sequence number of the last Log record, that changed the page.
M_xactreserved
This is the amount, the last added to the M_reservedcnt field.
M_xdesid
This is the internal ID of the most recent transaction, added to the M_reservedcnt field.
M_ghostreccnt
The is the count of Ghost Records on the page.
M_tornbits
This holds either the page checksum or the bits, were displaced by the Torn-page protection bits-depending on what F ORM of page protection is turnde on for the database.
Anatomy of the header structure of a SQL Server data file (MDF) page file
