Recently, I presented on SQL Server database “Boot Page” at Singapore SQL Pass Chapter user group. It was a great knowledge sharing experience, and It motivated me to share the knowledge to a wider audience. So, I will be blogging all the part of the demos with all possible details so that you enjoy the information whenever you would like to.

The aim of this series posts to get familiar with some of the relevant fields which may be helpful to DBA during troubleshooting. It is always good to understand the internal of your loved SQL Server database. Let’s start with basic understanding of the page;

The Boot Page is a special data page that exists only once per database. It stores critical metadata information about the database and acts as a brain of the database. It’s always page 9 of data file 1  (the first file in the PRIMARY filegroup)

If you explore a different-different version of SQL Server database boot page, you will find that number of fields on the page will vary version to version. You see lesser information on the older version of SQL Server database boot page compares to the latest version. It is mostly because of the new features fields which are being added on the page.

Before we start exploring it, I would like to highlight one very important point here – If you are playing directly with your production database metadata, for example; you have updated system catalog, rebuild the log file caused data loss or played with other metadata, you might end up in big trouble because most of the database critical activities get tracked by default on the boot page. If Microsoft product support team finds it, they may refuse to support your production database even you have a valid support contract with them. So, be careful!

To demonstrate, I shall be using SQL Server 2016 version and splitting out the output of the below-mentioned command into parts so that the explanation of the fields can fit into an image.

Let’s run the below mentioned undocumented command by selecting your database.

USE [KDSSGGROUP] – – Select your database
GO
DBCC TRACEON (3604) – – Send print commands to SSMS windows so that you see the result
DBCC DBINFO

First part of the output:

Second part of the output:

Third part of the output:

Here is complete out of the boot page;

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

DBINFO STRUCTURE:

DBINFO @0x000000AE694FD790

dbi_version = 852                   dbi_createVersion = 852             dbi_SEVersion = 0
dbi_dvSplitPoint = 0:0:0 (0x00000000:00000000:0000)
dbi_dbbackupLSN = 34:260:1 (0x00000022:00000104:0001)
dbi_LastLogBackupTime = 2016-11-07 07:06:38.400
dbi_nextseqnum = 1900-01-01 00:00:00.000                                 dbi_status = 0x00010000
dbi_crdate = 2016-11-07 07:03:40.777dbi_dbname = KDSSGGROUP             dbi_dbid = 5
dbi_cmptlevel = 130                 dbi_masterfixups = 0                dbi_maxDbTimestamp = 2000
dbi_dbbackupLSN = 34:260:1 (0x00000022:00000104:0001)                    dbi_RebuildLogs = 0
dbi_differentialBaseLSN = 34:189:102 (0x00000022:000000bd:0066)          dbi_RestoreFlags = 0x0000
dbi_checkptLSN = 34:264:1 (0x00000022:00000108:0001)                     dbi_dbccFlags = 2
dbi_COWLastLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_DirtyPageLSN = 34:264:1 (0x00000022:00000108:0001)                   dbi_RecoveryFlags = 0x00000000
dbi_lastxact = 0x36c                dbi_collation = 61448               dbi_relstat = 0x61000000
dbi_familyGUID = a23b599b-d949-49e7-8ac7-fcf5811c0a3c                    dbi_maxLogSpaceUsed = 483328

dbi_recoveryForkNameStack

entry 0

hex (dec) = 0x00000000:00000000:0000 (0:0:0)
m_guid = a23b599b-d949-49e7-8ac7-fcf5811c0a3c

entry 1

hex (dec) = 0x00000000:00000000:0000 (0:0:0)
m_guid = 00000000-0000-0000-0000-000000000000
dbi_differentialBaseGuid = 1fe3316d-c9a7-47ee-9a4c-b15e0e26317d          dbi_firstSysIndexes = 0001:00000014
dbi_oldestBackupXactLSN = 34:260:1 (0x00000022:00000104:0001)
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 = 34:189:102 (0x00000022:000000bd:0066)
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 = 2016-11-07 07:03:40.777
dbi_verRDB = 218105409              dbi_delayedDurabilityOption = 0
dbi_svcBrokerGUID = dd360cd1-bc45-4c08-9456-cd5957e86447                 dbi_svcBrokerOptions = 0x00000001
dbi_dbmLogZeroOutstanding = 0       dbi_dbmLastGoodRoleSequence = 0     dbi_dbmRedoQueue = 0
dbi_dbmRedoQueueType = 0            dbi_rmidRegistryValueDeleted = 0    dbi_dbmConnectionTimeout = 0
dbi_AuIdNext = 1099511627913        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:43:69 (0x00000012:0000002b:0045)
dbi_PendingRestoreOutcomesId = 00000000-0000-0000-0000-000000000000      dbi_ContainmentState = 0
dbi_hkRecoveryLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_hkLogTruncationLSN = 0:0:0 (0x00000000:00000000:0000)                dbi_hkCompatibilityMode = 0
dbi_hkRootFile = 00000000-0000-0000-0000-000000000000                    dbi_hkRootFileWatermark = 0
dbi_hkTrimLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_hkUpgradeLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_hkUndeployLSN = 0:0:0 (0x00000000:00000000:0000)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Hope, you enjoyed the learning!