SQLserver数据文件(MDF)的页面文件头结构剖析

发布时间：2020-12-12 14:56:51 所属栏目：MsSql教程来源：网络整理

导读：? SQLserver数据文件(MDF)的页面文件头结构剖析先执行一下以下SQL语句，我的测试环境为 SQL2005 dbcc traceon( 3604 ) go dbcc page(master, 1 , 0 , 2 ) 可以看到MDF文件的一些物理结构信息，其中包括重要的头96个字节。也就是第一个页面的文件头。 ......

00:0Fm_headerVersionm_typem_typeFlagBitsm_levelm_flagBitsm_indexIdm_prevPage(2)m_prevPage(1)pminlen10:1Fm_nextPage(2)m_nextPage(1)m_slotCntAllocUnitId.idObjm_freeCntm_freeData20:2Fm_pageId（2）m_pageId（1）m_reservedCntm_lsn(1)m_lsn(2)30:3Fm_lsn(3)m_xactReservedm_xdesId(2)m_xdesId(1)m_ghostRecCntm_tornBits

在数据库的头96个字节中第0x40开始直道0x5F应该都是0。

我发现只有测试页的m_pageId 的冒号前面的数字不为1时才在0x40到0x5f写入数据。但是具体代表什么还没有看出来。

姑且认为数据库第一个页面的0x00-0x3f就如上图所示，0x40-0x5f都为0（不正确的话请纠正一下）

这张图有什么用呢，如果你理解了上述参数的意义，用二进制编辑器打开一个头文件损坏的mdf文件就有可能恢复这个已经损坏的数据库。

偶不是dba也不是专业恢复数据的，只是个普通的开发人员，怎么恢复还请有经验人士补充一下。

有情提醒，这些东西非常危险，请不要随意测试，最好找一个没用的数据库来研究。

参数的意义

m_pageId

Thisidentifiesthefilenumberthepageispartofandthepositionwithinthefile.(1:143)meanspage143infile1.

m_headerVersion

Thisisthepageheaderversion.Sinceversion7.0thisvaluehasalwaysbeen1.

m_typea

Thisisthepagetype.Thevaluesyou'relikelytoseeare:

1-datapage.Thisholdsdatarecordsinaheaporclusteredindexleaf-level.

2-indexpage.Thisholdsindexrecordsintheupperlevelsofaclusteredindexandalllevelsofnon-clusteredindexes.

3-textmixpage.AtextpagethatholdssmallchunksofLOBvaluesplusinternalpartsoftexttree.ThesecanbesharedbetweenLOBvaluesinthesamepartitionofanindexorheap.

4-texttreepage.AtextpagethatholdslargechunksofLOBvaluesfromasinglecolumnvalue.

7-sortpage.Apagethatstoresintermediateresultsduringasortoperation.

8-GAMpage.HoldsglobalallocationinformationaboutextentsinaGAMinterval(everydatafileissplitinto4GBchunks-thenumberofextentsthatcanberepresentedinabitmaponasingledatabasepage).Basicallywhetheranextentisallocatedornot.GAM=GlobalAllocationMap.Thefirstoneispage2ineachfile.Moreontheseinalaterpost.

9-SGAMpage.HoldsglobalallocationinformationaboutextentsinaGAMinterval.Basicallywhetheranextentisavailableforallocatingmixed-pages.SGAM=SharedGAM.thefirstoneispage3ineachfile.Moreontheseinalaterpost.

10-IAMpage.HoldsallocationinformationaboutwhichextentswithinaGAMintervalareallocatedtoanindexorallocationunit,inSQLServer2000and2005respectively.IAM=IndexAllocationMap.Moreontheseinalaterpost.

11-PFSpage.HoldsallocationandfreespaceinformationaboutpageswithinaPFSinterval(everydatafileisalsosplitintoapprox64MBchunks-thenumberofpagesthatcanberepresentedinabyte-maponasingledatabasepage.PFS=PageFreeSpace.Thefirstoneispage1ineachfile.Moreontheseinalaterpost.

13-bootpage.Holdsinformationaboutthedatabase.There'sonlyoneoftheseinthedatabase.It'spage9infile1.

15-fileheaderpage.Holdsinformationaboutthefile.There'soneperfileandit'spage0inthefile.

16-diffmappage.HoldsinformationaboutwhichextentsinaGAMintervalhavechangedsincethelastfullordifferentialbackup.Thefirstoneispage6ineachfile.

17-MLmappage.HoldsinformationaboutwhichextentsinaGAMintervalhavechangedwhileinbulk-loggedmodesincethelastbackup.Thisiswhatallowsyoutoswitchtobulk-loggedmodeforbulk-loadsandindexrebuildswithoutworryingaboutbreakingabackupchain.Thefirstoneispage7ineachfile.

m_typeFlagBits

Thisismostlyunused.Fordataandindexpagesitwillalwaysbe4.Forallotherpagesitwillalwaysbe0-exceptPFSpages.IfaPFSpagehasm_typeFlagBitsof1,thatmeansthatatleastoneofthepagesinthePFSintervalmappedbythePFSpagehasatleastoneghostrecord.

m_level

Thisisthelevelthatthepageispartofintheb-tree.

Levelsarenumberedfrom0attheleaf-levelandincreasetothesingle-pagerootlevel(i.e.thetopoftheb-tree).

InSQLServer2000,theleaflevelofaclusteredindex(withdatapages)waslevel0,andthenextlevelup(withindexpages)wasalsolevel0.Thelevelthenincreasedtotheroot.SotodeterminewhetherapagewastrulyattheleaflevelinSQLServer2000,youneedtolookatthem_typeaswellasthem_level.

Forallpagetypesapartfromindexpages,thelevelisalways0.

m_flagBits

Thisstoresanumberofdifferentflagsthatdescribethepage.Forexample,0x200meansthatthepagehasapagechecksumonit(asourexamplepagedoes)and0x100meansthepagehastorn-pageprotectiononit.

SomebitsarenolongerusedinSQLServer2005.

m_objId

m_indexId

InSQLServer2000,theseidentifiedtheactualrelationalobjectandindexIDstowhichthepageisallocated.InSQLServer2005thisisnolongerthecase.Theallocationmetadatatotallychangedsotheseinsteadidentifywhat'scalledtheallocationunitthatthepagebelongsto(I'lldoanotherpostthatdescribestheselatertoday).

m_prevPage

m_nextPage

Thesearepointerstothepreviousandnextpagesatthisleveloftheb-treeandstore6-bytepageIDs.

Thepagesineachlevelofanindexarejoinedinadoubly-linkedlistaccordingtothelogicalorder(asdefinedbytheindexkeys)oftheindex.Thepointersdonotnecessarilypointtotheimmediatelyadjacentphysicalpagesinthefile(becauSEOffragmentation).

Thepagesontheleft-handsideofab-treelevelwillhavethem_prevPagepointerbeNULL,andthoSEOntheright-handsidewillhavethem_nextPagebeNULL.

Inaheap,orifanindexonlyhasasinglepage,thesepointerswillbothbeNULLforallpages.

pminlen

Thisisthesizeofthefixed-lengthportionoftherecordsonthepage.

m_slotCnt

Thisisthecountofrecordsonthepage.

m_freeCnt

Thisisthenumberofbytesoffreespaceinthepage.

m_freeData

Thisistheoffsetfromthestartofthepagetothefirstbyteaftertheendofthelastrecordonthepage.Itdoesn'tmatterifthereisfreespacenearertothestartofthepage.

m_reservedCnt

Thisisthenumberofbytesoffreespacethathasbeenreservedbyactivetransactionsthatfreedupspaceonthepage.Itpreventsthefreespacefrombeingusedupandallowsthetransactionstoroll-backcorrectly.There'saverycomplicatedalgorithmforchangingthisvalue.

m_lsn

ThisistheLogSequenceNumberofthelastlogrecordthatchangedthepage.

m_xactReserved

Thisistheamountthatwaslastaddedtothem_reservedCntfield.

m_xdesId

ThisistheinternalIDofthemostrecenttransactionthataddedtothem_reservedCntfield.

m_ghostRecCnt

Theisthecountofghostrecordsonthepage.

m_tornBits

Thisholdseitherthepagechecksumorthebitsthatweredisplacedbythetorn-pageprotectionbits-dependingonwhatformofpageprotectionisturndeonforthedatabase.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

SQLserver数据文件(MDF)的页面文件头结构剖析

先执行一下以下SQL语句，我的测试环境为SQL2005

dbcc traceon( 3604 ) go

dbcc page(master, 1 , 0 , 2 )

可以看到MDF文件的一些物理结构信息，其中包括重要的头96个字节。也就是第一个页面的文件头。

........

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) = 99 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 ?................

以上蓝色的文字就是文件头的一些信息。如果这些信息损坏将会造成严重的后果。

经过简单的逐个字节分析，中间借助了windows计算器和c#的BitConverter.GetBytes函数。得出了如下文件结构图，其中每行4个字节，一共分析了文件头的前64个字节。