The Secret of the Database Boot Page

2014-05-28 - General, Series, SQL Server Internals, Storage Wednesday

Introduction

There is one special data page that exists only once per database. It is the database boot page. The database boot page is always stored in page 9 of file 1, the first file in the primary file group.

The Database Boot Page Examined

As with many other internal SQL Server structures, the database boot page is not documented. In most cases, you probably will not even need to ever look at it. But, if the need arises it is always good to be prepared.

The database boot page contains a broad variety of data about the database itself. Here you can find the internal database version, the compatibility level, the database name and the date of the last log backup. But, you can also find more obscure values, like the one that is just called "safety". It is set to 0 in my database. I hope that is not a bad sign...

To look into this page, we can use the same DBCC PAGE command we have used with the other page types. I am not aware of any special command like DBCC FILEHEADER that would produce an easier to read output of the database boot page, but new undocumented DBCC Commands still keep appearing every once in a while.

So let us actually take a look at this page:

DBCC TRACEON(3604);
DBCC PAGE(0,1,9,3);

As always, DBCC PAGE requires trace flag 3604 to be turned on for the connection.

The image below shows part of the output:

The database boot page.

You can see that the page type of the database boot page is 13. The full output is shown below:

[sourcecode] DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (1:9)

BUFFER:

BUF @0x00000000041F1740

bpage = 0x00000001F149A000 bhash = 0x0000000000000000 bpageno = (1:9)
bdbid = 16 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 50077 bstat = 0x9
blog = 0x15ab215a bnext = 0x0000000000000000

PAGE HEADER:

Page @0x00000001F149A000

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 = (286:240:2) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 1246961098
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 @0x000000006F8BA060

0000000000000000: 0000e005 c202c202 00000000 00000000 00000000 ..à.Â.Â.............
0000000000000014: 9847bc00 1ca30000 00000000 00000000 00000100 .G¼..£..............
0000000000000028: 755c907b 88ee8d00 b7a20000 49006d00 61006700 u\.{.î..•¢..I.m.a.g.
000000000000003C: 65002000 a9007300 71006c00 69007400 79002e00 e. .©.s.q.l.i.t.y...
0000000000000050: 6e006500 74002000 32003000 31003400 20202020 n.e.t. .2.0.1.4.
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 2c000000 10006e00 d0070000 ,.....n.Ð...
0000000000000140: 00000000 b3000000 24020000 7a000000 1d010000 ....³...$...z.......
0000000000000154: ff070000 25000000 1e010000 ed000000 03000200 ÿ...%.......í.......
0000000000000168: 00000000 00000000 00000000 1e010000 ed000000 ................í...
000000000000017C: 03000000 39aa0000 00000000 08d00034 00000000 ....9ª.......Ð.4....
0000000000000190: 00000061 00000000 8fe3a2de 3ec3a94a 81a7dc8a ...a.....ã¢Þ>éJ.§ÜŠ
00000000000001A4: f11beec8 000a5404 00000000 00000000 00000000 ñ.îÈ..T.............
00000000000001B8: 00000000 00000000 00000000 00000000 8fe3a2de .................ã¢Þ
00000000000001CC: 3ec3a94a 81a7dc8a f11beec8 00000000 00000000 >éJ.§ÜŠñ.îÈ........
00000000000001E0: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001F4: 2904189c 4d58074f b5c49fb4 7480147d 14000000 )..œMX.OµÄŸ´t..}....
0000000000000208: 01006302 00000000 00000000 00000000 00000000 ..c.................
000000000000021C: 00000000 00000400 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: b3000000 24020000 7a000000 00000000 00000000 ³...$...z...........
0000000000000294: 00000000 00000000 00000000 00000000 88ee8d00 .................î..
00000000000002A8: b7a20000 b80b000b 00000000 12c47174 bcd2124d •¢..¸........Äqt¼Ò.M
00000000000002BC: 9ae2e518 f49bdba2 01000000 00000000 00000000 šâå.ô›Û¢............
00000000000002D0: 00000000 00000000 00000000 00000000 fb000000 ................û...
00000000000002E4: 00010000 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 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 12000000 51000000 ................Q...
00000000000005B4: 43000000 00000000 00000000 00000000 01000000 C...................
00000000000005C8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000005DC: 00000000 ....

DBINFO @0x000000006F8BA060

dbi_version = 706 dbi_createVersion = 706 dbi_SEVersion = 0
dbi_dvSplitPoint = 0:0:0 (0x00000000:00000000:0000)
dbi_dbbackupLSN = 179:548:122 (0x000000b3:00000224:007a)
dbi_LastLogBackupTime = 2014-04-29 11:25:30.320
dbi_nextseqnum = 1900-01-01 00:00:00.000 dbi_status = 0x00010000
dbi_crdate = 2014-01-18 08:36:45.467dbi_dbname = Image ©sqlity.net 2014 dbi_dbid = 16
dbi_cmptlevel = 110 dbi_masterfixups = 0 dbi_maxDbTimestamp = 2000
dbi_dbbackupLSN = 179:548:122 (0x000000b3:00000224:007a) dbi_RebuildLogs = 0
dbi_differentialBaseLSN = 285:2047:37 (0x0000011d:000007ff:0025) dbi_RestoreFlags = 0x0000
dbi_checkptLSN = 286:237:3 (0x0000011e:000000ed:0003) dbi_dbccFlags = 2
dbi_COWLastLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_DirtyPageLSN = 286:237:3 (0x0000011e:000000ed:0003) dbi_RecoveryFlags = 0x00000000
dbi_lastxact = 0xaa39 dbi_collation = 872468488 dbi_relstat = 0x61000000
dbi_familyGUID = dea2e38f-c33e-4aa9-81a7-dc8af11beec8 dbi_maxLogSpaceUsed = 72616448

dbi_recoveryForkNameStack

entry 0

hex (dec) = 0x00000000:00000000:0000 (0:0:0)
m_guid = dea2e38f-c33e-4aa9-81a7-dc8af11beec8

entry 1

hex (dec) = 0x00000000:00000000:0000 (0:0:0)
m_guid = 00000000-0000-0000-0000-000000000000
dbi_differentialBaseGuid = 9c180429-584d-4f07-b5c4-9fb47480147d dbi_firstSysIndexes = 0001:00000014
dbi_oldestBackupXactLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_versionChangeLSN = 0:0:0 (0x00000000:00000000:0000) dbi_mdUpgStat = 0x0004
dbi_category = 0x0000000000000000 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 = 179:548:122 (0x000000b3:00000224:007a)
dbi_dbccLastKnownGood = 1900-01-01 00:00:00.000 dbi_roleSequence = 0
dbi_dbmHardenedLsn = 0:0:0 (0x00000000:00000000:0000) dbi_localState = 0
dbi_safety = 0 dbi_modDate = 2014-01-18 08:36:45.467
dbi_verRDB = 184552376 dbi_lazyCommitOption = 0
dbi_svcBrokerGUID = 7471c412-d2bc-4d12-9ae2-e518f49bdba2 dbi_svcBrokerOptions = 0x00000001
dbi_dbmLogZeroOutstanding = 0 dbi_dbmLastGoodRoleSequence = 0 dbi_dbmRedoQueue = 0
dbi_dbmRedoQueueType = 0 dbi_rmidRegistryValueDeleted = 0 dbi_dbmConnectionTimeout = 0
dbi_fragmentId = 0 dbi_AuIdNext = 1099511628027
dbi_MinSkipLsn = 0:0:0 (0x00000000:00000000:0000) dbi_commitTsOfcheckptLSN = 0
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 = 18:81:67 (0x00000012:00000051:0043) dbi_splitAGE = 0
dbi_PendingRestoreOutcomesId = 00000000-0000-0000-0000-000000000000 dbi_ContianmentState = 1

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
[/sourcecode]

Summary

Every database contains a single database boot page to store database settings and configuration values. You can always find this special page in page 9 of the first data file.

Categories: General, Series, SQL Server Internals, Storage Wednesday
Tags: , , ,

Leave a Reply