SQL Server通过File Header Page来进行Crash Recovery
看了盖总的一篇文章
http://www.eygle.com/archives/2008/11/oracle_internals_preface.html
数据文件的第一个Block记录了重要的检查点、SCN等信息，这些信息在启动时要被读取，这里就是这样一种体现。
我们看一下SQL Server的情况，使用DBCC fileheader命令来读取file header page，编号为10是我的一个用户库SSS的数据库ID
环境：SQL Server2012 64位
SELECT DB_ID('sss')
DBCC fileheader(10)
（图一）
（图二）
（图三）
（图四）
从上面的图大家应该发现一些规律
RecoveryForkLSN：Cash Recovery的时候需要调用的lsn，也是RedoStartLSN：27000000027100001
ReadOnlyLsn：只读模式数据库
ReadWriteLsn：读写模式数据库
因为file header page是每个数据库的mdf文件的第一个页面，启动的过程里面其实最重要也是最耗时就是数据库redo和undo过程
所以把Redo Start LSN放在file header page也就不无道理了，数据库一启动马上就读取LSN信息，然后到LDF文件里读取日志
数据库第0页:file header page
数据库第1页：PFS (Page Free Space) ,也叫页面*空间
数据库第2页：GAM 全局分配映射(Global Allocation Map，GAM)页面
数据库第3页：SGAM 共享全局分配映射(Shared Global Allocation Map，SGAM)页面
第4页没有数据
第5页没有数据
数据库第6页：DCM 差异变更(Differential Changed Map，DCM)页面
数据库第7页：BCM 批量更改映射（Bulk Changed Map）页面
数据库第8页：sys.sysqnames 存在于每个数据库中。
数据库第9页：boot page 数据库根据这个页面的信息来启动的
boot page是放在第9页不是放在第0页，为什麽呢？因为数据库先要进行Cash Recovery才能启动啊
--看一下file header page DBCC TRACEON(3604,-1)
GO DBCC PAGE([sss],1,0,3)
GO
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 PAGE: (1:0) BUFFER: BUF @0x00000002FDABA800 bpage = 0x00000002EFF88000 bhash = 0x0000000000000000 bpageno = (1:0)
bdbid = 10 breferences = 0 bcputicks = 88
bsampleCount = 1 bUse1 = 36811 bstat = 0x9
blog = 0x15ab215a bnext = 0x0000000000000000 PAGE HEADER: Page @0x00000002EFF88000 m_pageId = (1:0) m_headerVersion = 1 m_type = 15
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x208
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 = 7029 m_freeData = 7551
m_reservedCnt = 0 m_lsn = (878:901:1) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = -582568961
DB Frag ID = 1 Allocation Status GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED File Header Data: Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 1065
Memory Dump @0x000000000D1BB956 0000000000000000: 30000800 00000000 31000000 00000000 002e007f 0.......1...........
0000000000000014: 007f0081 00830087 008b008f 0093009d 00a700b1 ....................
0000000000000028: 00b100b5 00b900bd 00c100cb 00e700f1 00fb0005 ....................
000000000000003C: 0115011f 012f0133 013d013d 01430153 01530153 ...../.3.=.=.C.S.S.S
0000000000000050: 01530153 01530153 01630163 0163016d 01770193 .S.S.S.S.c.c.c.m.w..
0000000000000064: 019d01ad 01c901d1 012904c5 cbddcf11 44c34889 .........)......D.H.
0000000000000078: 52cc552b 3eba7601 00010080 c00500ff ffffff80 R.U+>.v.............
000000000000008C: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000A0: 00000000 00000000 00000000 00000000 00800100 ....................
00000000000000B4: 00000000 00ffffff ff000200 001b0000 000f0100 ....................
00000000000000C8: 0001001b 0000000f 01000001 00000053 d02a7787 ...............S.*w.
00000000000000DC: 3ec94e97 926f64fe 3febf81b 000000c3 000000b2 >.N..od.?...........
00000000000000F0: 00000000 00000000 0000006e 0300006d 0300002e ...........n...m....
0000000000000104: 00284a9c 892994c1 4692f4f1 e3c51d34 90000000 .(J..)..F......4....
0000000000000118: 00000000 00000014 51b676d9 b1a34abc c0185ae9 ........Q.v...J...Z.
000000000000012C: 6fccb108 0000001b 0000000f 01000001 00730073 o................s.s
0000000000000140: 0073006a 49d8681b 194f469a 40c068d8 57651953 .s.jI.h..OF.@.h.We.S
0000000000000154: d02a7787 3ec94e97 926f64fe 3febf800 00000000 .*w.>.N..od.?.......
0000000000000168: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000017C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000190: 0000001b 000000c3 000000b2 006a49d8 681b194f .............jI.h..O
00000000000001A4: 469a40c0 68d85765 19000000 00000000 00000000 F.@.h.We............
00000000000001B8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001CC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001E0: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001F4: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000208: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000021C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000230: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000244: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000258: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000026C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000280: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000294: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002A8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002BC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002D0: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002E4: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002F8: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000030C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000320: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000334: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000348: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000035C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000370: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000384: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000398: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003AC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003C0: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003D4: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003E8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003FC: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000410: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000424: 00000000 00 .....
BindingID = cfddcbc5-4411-48c3-8952-cc552b3eba76 FileIdProp = 1
FileGroupId = 1 Size = 49280 MaxSize = 65535
Growth = 128 Perf = 0 BackupLsn = (0:0:0)
FirstUpdateLsn = (0:0:0) OldestRestoredLsn = (0:0:0) FirstNonloggedUpdateLsn = [NULL]
MinSize = 384 Status = 0 UserShrinkSize = 65535
SectorSize = 512 MaxLsn = (27:271:1) FirstLsn = (27:195:178)
CreateLsn = (0:0:0) DifferentialBaseLsn = (878:877:46)
DifferentialBaseGuid = 899c4a28-9429-46c1-92f4-f1e3c51d3490 FileOfflineLsn = (0:0:0)
FileIdGuid = 76b65114-b1d9-4aa3-bcc0-185ae96fccb1 RestoreStatus = 8
RestoreRedoStartLsn = (27:271:1) RestoreSourceGuid = 68d8496a-191b-464f-9a40-c068d8576519
HardenedSkipLsn = [NULL] ReplTxfTruncationLsn = [NULL] TxfBackupLsn = [NULL]
FstrContainerSize = [NULL] MaxLsnBranchId = 772ad053-3e87-4ec9-9792-6f64fe3febf8
SecondaryRedoStartLsn = [NULL] SecondaryDifferentialBaseLsn = [NULL]
ReadOnlyLsn = (0:0:0) ReadWriteLsn = (0:0:0)
RestoreDifferentialBaseLsn = (27:195:178)
RestoreDifferentialBaseGuid = 68d8496a-191b-464f-9a40-c068d8576519
RestorePathOrigin hex (dec) = 0x00000000:00000000:0000 (0:0:0)
m_guid = 00000000-0000-0000-0000-000000000000
DatabaseEncryptionFileState.m_maxScannedPage = 0 DatabaseEncryptionFileState.m_keyId = 0 FCBFileDEK m_dbeStatusBits = 0 m_dtCreated = 1900-01-01 00:00:00.000
m_dtLastRegenerated = 1900-01-01 00:00:00.000
m_dtLastModified = 1900-01-01 00:00:00.000 m_dtLastSet = 1900-01-01 00:00:00.000
m_dtOpened = 1900-01-01 00:00:00.000m_algId = 0 m_algId = 0
m_dwBitLen = 0 m_cbThumbprint = 0 m_rgbThumbprint = 0x DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
--看一下boot page的内容
DBCC TRACEON(3604,-1)
GO DBCC PAGE([sss],1,9,3)
GO
Page @0x00000002EA7DE000 m_pageId = (1:9) m_headerVersion = 1 m_type = 13
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x200
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 = 6590 m_freeData = 1600
m_reservedCnt = 0 m_lsn = (878:1345:2) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 2000067799
DB Frag ID = 1 Allocation Status GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED Slot 0, Offset 0x60, Length 1504, DumpStyle BYTE Record Type = PRIMARY_RECORD Record Attributes = Record Size = 1504 Memory Dump @0x000000000D1BA060 0000000000000000: 0000e005 c2026302 00000000 00000000 00000000 ......c.............
0000000000000014: 00000000 00000000 00000000 00000000 08008100 ....................
0000000000000028: ae80847c bba19900 0ca30000 73007300 73002020 ...|........s.s.s.
000000000000003C: 20202020 20202020 20202020 20202020 20202020
0000000000000050: 20202020 20202020 20202020 20202020 20202020
0000000000000064: 20202020 20202020 20202020 20202020 20202020
0000000000000078: 20202020 20202020 20202020 20202020 20202020
000000000000008C: 20202020 20202020 20202020 20202020 20202020
00000000000000A0: 20202020 20202020 20202020 20202020 20202020
00000000000000B4: 20202020 20202020 20202020 20202020 20202020
00000000000000C8: 20202020 20202020 20202020 20202020 20202020
00000000000000DC: 20202020 20202020 20202020 20202020 20202020
00000000000000F0: 20202020 20202020 20202020 20202020 20202020
0000000000000104: 20202020 20202020 20202020 20202020 20202020
0000000000000118: 20202020 20202020 20202020 20202020 20202020
000000000000012C: 20202020 20202020 06000000 0a006e00 70170000 ......n.p...
0000000000000140: 00000000 00000000 00000000 00000000 6e030000 ................n...
0000000000000154: 6d030000 2e004000 6e030000 3a050000 0c000200 m.....@.n...:.......
0000000000000168: 00000000 00000000 00000000 6e030000 3a050000 ............n...:...
000000000000017C: 0c000000 c2271400 00000000 24d00000 00000000 .....'......$.......
0000000000000190: 00000041 00000000 53d02a77 873ec94e 97926f64 ...A....S.*w.>.N..od
00000000000001A4: fe3febf8 00e67dbf 00000000 00000000 00000000 .?....}.............
00000000000001B8: 00000000 1b000000 0f010000 01000000 d305e86e ...................n
00000000000001CC: 6e28d54a 892e8070 c5aec1fa 1b000000 0f010000 n(.J...p............
00000000000001E0: 01000000 53d02a77 873ec94e 97926f64 fe3febf8 ....S.*w.>.N..od.?..
00000000000001F4: 284a9c89 2994c146 92f4f1e3 c51d3490 14000000 (J..)..F......4.....
0000000000000208: 01006302 00000000 00000000 00000000 22000000 ..c............."...
000000000000021C: d0000000 03000400 80000000 00000000 00000000 ....................
0000000000000230: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000244: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000258: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000026C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000280: c9020000 1e000000 e3000000 c3f9a100 bda30000 ....................
0000000000000294: 00000000 00000000 00000000 00000000 553cda00 ................U<..
00000000000002A8: 05a30000 b80b000b 00000000 59123e95 5d4a114b ............Y.>.]J.K
00000000000002BC: b9a2abb7 5245120c 00000000 00000000 00000000 ....RE..............
00000000000002D0: 00000000 00000000 00000000 00000000 96010000 ....................
00000000000002E4: 00010000 00000000 00000000 00000000 02000000 ....................
00000000000002F8: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000030C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000320: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000334: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000348: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000035C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000370: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000384: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000398: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003AC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003C0: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003D4: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003E8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003FC: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000410: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000424: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000438: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000044C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000460: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000474: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000488: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000049C: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000004B0: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000004C4: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000004D8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000004EC: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000500: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000514: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000528: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000053C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000550: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000564: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000578: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000058C: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000005A0: 00000000 00000000 00000000 20000000 78000000 ............ ...x...
00000000000005B4: 43000000 00000000 00000000 00000000 00000000 C...................
00000000000005C8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000005DC: 00000000 .... DBINFO @0x000000000D1BA060 dbi_version = 706 dbi_createVersion = 611 dbi_SEVersion = 0
dbi_dvSplitPoint = 0:0:0 (0x00000000:00000000:0000)
dbi_dbbackupLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_LastLogBackupTime = 1900-01-01 00:00:00.000
dbi_nextseqnum = 1900-01-01 00:00:00.000 dbi_status = 0x00810008
dbi_crdate = 2014-04-13 09:19:21.370dbi_dbname = sss dbi_dbid = 10
dbi_cmptlevel = 110 dbi_masterfixups = 0 dbi_maxDbTimestamp = 6000
dbi_dbbackupLSN = 0:0:0 (0x00000000:00000000:0000) dbi_RebuildLogs = 0
dbi_differentialBaseLSN = 878:877:46 (0x0000036e:0000036d:002e) dbi_RestoreFlags = 0x0040
dbi_checkptLSN = 878:1338:12 (0x0000036e:0000053a:000c) dbi_dbccFlags = 2
dbi_COWLastLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_DirtyPageLSN = 878:1338:12 (0x0000036e:0000053a:000c) dbi_RecoveryFlags = 0x00000000
dbi_lastxact = 0x1427c2 dbi_collation = 53284 dbi_relstat = 0x41000000
dbi_familyGUID = 772ad053-3e87-4ec9-9792-6f64fe3febf8 dbi_maxLogSpaceUsed = 3212699136 dbi_recoveryForkNameStack entry 0 hex (dec) = 0x0000001b:0000010f:0001 (27:271:1)
m_guid = 6ee805d3-286e-4ad5-892e-8070c5aec1fa entry 1 hex (dec) = 0x0000001b:0000010f:0001 (27:271:1)
m_guid = 772ad053-3e87-4ec9-9792-6f64fe3febf8
dbi_differentialBaseGuid = 899c4a28-9429-46c1-92f4-f1e3c51d3490 dbi_firstSysIndexes = 0001:00000014
dbi_oldestBackupXactLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_versionChangeLSN = 34:208:3 (0x00000022:000000d0:0003) dbi_mdUpgStat = 0x0004
dbi_category = 0x0000000000000080 dbi_safetySequence = 0
dbi_dbMirrorId = 00000000-0000-0000-0000-000000000000
dbi_pageUndoLsn = 0:0:0 (0x00000000:00000000:0000) dbi_pageUndoState = 0
dbi_disabledSequence = 0 dbi_dbmRedoLsn = 0:0:0 (0x00000000:00000000:0000)
dbi_dbmOldestXactLsn = 0:0:0 (0x00000000:00000000:0000) dbi_CloneCpuCount = 0
dbi_CloneMemorySize = 0 dbi_updSysCatalog = 1900-01-01 00:00:00.000
dbi_LogBackupChainOrigin = 713:30:227 (0x000002c9:0000001e:00e3)
dbi_dbccLastKnownGood = 2014-10-07 09:49:44.117 dbi_roleSequence = 0
dbi_dbmHardenedLsn = 0:0:0 (0x00000000:00000000:0000) dbi_localState = 0
dbi_safety = 0 dbi_modDate = 2014-04-06 13:14:34.310
dbi_verRDB = 184552376 dbi_lazyCommitOption = 0
dbi_svcBrokerGUID = 953e1259-4a5d-4b11-b9a2-abb75245120c dbi_svcBrokerOptions = 0x00000000
dbi_dbmLogZeroOutstanding = 0 dbi_dbmLastGoodRoleSequence = 0 dbi_dbmRedoQueue = 0
dbi_dbmRedoQueueType = 0 dbi_rmidRegistryValueDeleted = 0 dbi_dbmConnectionTimeout = 0
dbi_fragmentId = 0 dbi_AuIdNext = 1099511628182
dbi_MinSkipLsn = 0:0:0 (0x00000000:00000000:0000) dbi_commitTsOfcheckptLSN = 2
dbi_dbEmptyVersionState = 0 dbi_CurrentGeneration = 0
dbi_EncryptionHistory Scan 0 hex (dec) = 0x00000000:00000000:0000 (0:0:0)
EncryptionScanInfo:ScanId = 0 Scan 1 hex (dec) = 0x00000000:00000000:0000 (0:0:0)
EncryptionScanInfo:ScanId = 0 Scan 2 hex (dec) = 0x00000000:00000000:0000 (0:0:0)
EncryptionScanInfo:ScanId = 0
dbi_latestVersioningUpgradeLSN = 32:120:67 (0x00000020:00000078:0043) dbi_splitAGE = 0
dbi_PendingRestoreOutcomesId = 00000000-0000-0000-0000-000000000000 dbi_ContianmentState = 0
相关文章
查看SQLSERVER内部数据页面的小插件Internals Viewer
如有不对的地方，欢迎大家拍砖o(∩_∩)o