SQL Server進行Crash Recovery

lightwing發表於2021-09-09

我們看一下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 pageDBCC TRACEON(3604,-1)GODBCC PAGE([sss],1,0,3)GO

圖片描述圖片描述

DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊,請與系統管理員聯絡。PAGE: (1:0)BUFFER:BUF @0x00000002FDABA800bpage = 0x00000002EFF88000          bhash = 0x0000000000000000          bpageno = (1:0)bdbid = 10                          breferences = 0                     bcputicks = 88bsampleCount = 1                    bUse1 = 36811                       bstat = 0x9blog = 0x15ab215a                   bnext = 0x0000000000000000          PAGE HEADER:Page @0x00000002EFF88000m_pageId = (1:0)                    m_headerVersion = 1                 m_type = 15m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x208m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 0m_slotCnt = 1                       m_freeCnt = 7029                    m_freeData = 7551m_reservedCnt = 0                   m_lsn = (878:901:1)                 m_xactReserved = 0m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = -582568961DB Frag ID = 1                      Allocation StatusGAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULLDIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           File Header Data:Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNSRecord Size = 1065                  Memory Dump @0x000000000D1BB9560000000000000000:   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.S0000000000000050:   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.s0000000000000140:   0073006a 49d8681b 194f469a 40c068d8 57651953  .s.jI.h..OF.@.h.We.S0000000000000154:   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..O00000000000001A4:   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 = 1FileGroupId = 1                     Size = 49280                        MaxSize = 65535Growth = 128                        Perf = 0                            BackupLsn = (0:0:0)FirstUpdateLsn = (0:0:0)            OldestRestoredLsn = (0:0:0)         FirstNonloggedUpdateLsn = [NULL]MinSize = 384                       Status = 0                          UserShrinkSize = 65535SectorSize = 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 = 8RestoreRedoStartLsn = (27:271:1)    RestoreSourceGuid = 68d8496a-191b-464f-9a40-c068d8576519HardenedSkipLsn = [NULL]            ReplTxfTruncationLsn = [NULL]       TxfBackupLsn = [NULL]FstrContainerSize = [NULL]          MaxLsnBranchId = 772ad053-3e87-4ec9-9792-6f64fe3febf8SecondaryRedoStartLsn = [NULL]      SecondaryDifferentialBaseLsn = [NULL]ReadOnlyLsn = (0:0:0)               ReadWriteLsn = (0:0:0)              RestoreDifferentialBaseLsn = (27:195:178)                                RestoreDifferentialBaseGuid = 68d8496a-191b-464f-9a40-c068d8576519       RestorePathOriginhex (dec) = 0x00000000:00000000:0000 (0:0:0)                             m_guid = 00000000-0000-0000-0000-000000000000                            DatabaseEncryptionFileState.m_maxScannedPage = 0                         DatabaseEncryptionFileState.m_keyId = 0FCBFileDEKm_dbeStatusBits = 0                 m_dtCreated = 1900-01-01 00:00:00.000m_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.000m_dtOpened = 1900-01-01 00:00:00.000m_algId = 0                         m_algId = 0m_dwBitLen = 0                      m_cbThumbprint = 0                  m_rgbThumbprint = 0xDBCC 執行完畢。如果 DBCC 輸出了錯誤資訊,請與系統管理員聯絡。

圖片描述

 

圖片描述

--看一下boot page的內容DBCC TRACEON(3604,-1)GODBCC PAGE([sss],1,9,3)GO

圖片描述圖片描述

Page @0x00000002EA7DE000m_pageId = (1:9)                    m_headerVersion = 1                 m_type = 13m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x200m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 0m_slotCnt = 1                       m_freeCnt = 6590                    m_freeData = 1600m_reservedCnt = 0                   m_lsn = (878:1345:2)                m_xactReserved = 0m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = 2000067799DB Frag ID = 1                      Allocation StatusGAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL                        DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED           Slot 0, Offset 0x60, Length 1504, DumpStyle BYTERecord Type = PRIMARY_RECORD        Record Attributes =                 Record Size = 1504Memory Dump @0x000000000D1BA0600000000000000000:   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..od00000000000001A4:   fe3febf8 00e67dbf 00000000 00000000 00000000  .?....}.............00000000000001B8:   00000000 1b000000 0f010000 01000000 d305e86e  ...................n00000000000001CC:   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:>.]J.K00000000000002BC:   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 @0x000000000D1BA060dbi_version = 706                   dbi_createVersion = 611             dbi_SEVersion = 0dbi_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 = 0x00810008dbi_crdate = 2014-04-13 09:19:21.370dbi_dbname = sss                    dbi_dbid = 10dbi_cmptlevel = 110                 dbi_masterfixups = 0                dbi_maxDbTimestamp = 6000dbi_dbbackupLSN = 0:0:0 (0x00000000:00000000:0000)                       dbi_RebuildLogs = 0dbi_differentialBaseLSN = 878:877:46 (0x0000036e:0000036d:002e)          dbi_RestoreFlags = 0x0040dbi_checkptLSN = 878:1338:12 (0x0000036e:0000053a:000c)                  dbi_dbccFlags = 2dbi_COWLastLSN = 0:0:0 (0x00000000:00000000:0000)                        dbi_DirtyPageLSN = 878:1338:12 (0x0000036e:0000053a:000c)                dbi_RecoveryFlags = 0x00000000dbi_lastxact = 0x1427c2             dbi_collation = 53284               dbi_relstat = 0x41000000dbi_familyGUID = 772ad053-3e87-4ec9-9792-6f64fe3febf8                    dbi_maxLogSpaceUsed = 3212699136dbi_recoveryForkNameStackentry 0hex (dec) = 0x0000001b:0000010f:0001 (27:271:1)                          m_guid = 6ee805d3-286e-4ad5-892e-8070c5aec1fa                            entry 1hex (dec) = 0x0000001b:0000010f:0001 (27:271:1)                          m_guid = 772ad053-3e87-4ec9-9792-6f64fe3febf8                            dbi_differentialBaseGuid = 899c4a28-9429-46c1-92f4-f1e3c51d3490          dbi_firstSysIndexes = 0001:00000014dbi_oldestBackupXactLSN = 0:0:0 (0x00000000:00000000:0000)               dbi_versionChangeLSN = 34:208:3 (0x00000022:000000d0:0003)               dbi_mdUpgStat = 0x0004dbi_category = 0x0000000000000080   dbi_safetySequence = 0              dbi_dbMirrorId = 00000000-0000-0000-0000-000000000000                    dbi_pageUndoLsn = 0:0:0 (0x00000000:00000000:0000)                       dbi_pageUndoState = 0dbi_disabledSequence = 0            dbi_dbmRedoLsn = 0:0:0 (0x00000000:00000000:0000)dbi_dbmOldestXactLsn = 0:0:0 (0x00000000:00000000:0000)                  dbi_CloneCpuCount = 0dbi_CloneMemorySize = 0             dbi_updSysCatalog = 1900-01-01 00:00:00.000dbi_LogBackupChainOrigin = 713:30:227 (0x000002c9:0000001e:00e3)         dbi_dbccLastKnownGood = 2014-10-07 09:49:44.117                          dbi_roleSequence = 0dbi_dbmHardenedLsn = 0:0:0 (0x00000000:00000000:0000)                    dbi_localState = 0dbi_safety = 0                      dbi_modDate = 2014-04-06 13:14:34.310dbi_verRDB = 184552376              dbi_lazyCommitOption = 0            dbi_svcBrokerGUID = 953e1259-4a5d-4b11-b9a2-abb75245120c                 dbi_svcBrokerOptions = 0x00000000dbi_dbmLogZeroOutstanding = 0       dbi_dbmLastGoodRoleSequence = 0     dbi_dbmRedoQueue = 0dbi_dbmRedoQueueType = 0            dbi_rmidRegistryValueDeleted = 0    dbi_dbmConnectionTimeout = 0dbi_fragmentId = 0                  dbi_AuIdNext = 1099511628182        dbi_MinSkipLsn = 0:0:0 (0x00000000:00000000:0000)                        dbi_commitTsOfcheckptLSN = 2dbi_dbEmptyVersionState = 0         dbi_CurrentGeneration = 0           dbi_EncryptionHistoryScan 0hex (dec) = 0x00000000:00000000:0000 (0:0:0)                             EncryptionScanInfo:ScanId = 0       Scan 1hex (dec) = 0x00000000:00000000:0000 (0:0:0)                             EncryptionScanInfo:ScanId = 0       Scan 2hex (dec) = 0x00000000:00000000:0000 (0:0:0)                             EncryptionScanInfo:ScanId = 0       dbi_latestVersioningUpgradeLSN = 32:120:67 (0x00000020:00000078:0043)    dbi_splitAGE = 0dbi_PendingRestoreOutcomesId = 00000000-0000-0000-0000-000000000000      dbi_ContianmentState = 0

圖片描述

 

 


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/3407/viewspace-2812949/,如需轉載,請註明出處,否則將追究法律責任。

相關文章