Where are sp_configure settings stored? Another reason to backup master…

Earlier today I blogged on our SQL Server Pro magazine blog about false-alarm corruptions you will *definitely* see if you restore a backup of the master database as a user database and run DBCC CHECKDB on the restored copy. You might be doing this as part of offloading consistency checks to another server or validating your backups restore correctly and contain an undamaged database. You'll definitely get a shock the first time you see these false-alarms and think your real master is corrupt!

One of the 'corruptions' is because of a special page that only exists in the master database – the CONFIG block of the SQL Server instance. It's page number 10 in data file ID 1 of master. DBCC CHECKDB flags this as a corruption if it's present in any other database apart from master, as the page in master is marked as allocated, but no object owns it. This is only allowed in master. Similar allowances are made by DBCC CHECKDB in every database for the boot page (page 9 in file 1) and for each data and log file's file header page (page 0 in each file). See here for a blog post about the boot page and here for a blog post about file header pages.

What does this special page store? It stores all the sp_configure options for the SQL Server instance. This is yet one more reason to make sure you regularly back up the master database because if you lose master then you lose all your configuration settings for that SQL Server instance.

You can examine this page using DBCC PAGE or the equally undocumented, but nowhere nearly as well known, DBCC CONFIG command (they use the same code under the covers and both require trace flag 3604).

Here's an example from one of my laptop instances:

DBCC TRACEON (3604);
DBCC PAGE ('master', 1, 10, 3);
GO

PAGE: (1:10)

BUFFER:

BUF @0x0000000080FC0AC0

bpage = 0x0000000080572000           bhash = 0x0000000000000000           bpageno = (1:10)
bdbid = 1                            breferences = 0                      bcputicks = 264
bsampleCount = 1                     bUse1 = 23062                        bstat = 0xc00009
blog = 0x32159                       bnext = 0x0000000000000000          

PAGE HEADER:

Page @0x0000000080572000

m_pageId = (1:10)                    m_headerVersion = 1                  m_type = 14
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 = 0                        m_freeData = 8190
m_reservedCnt = 0                    m_lsn = (0:0:1)                      m_xactReserved = 0
m_xdesId = (0:0)                     m_ghostRecCnt = 0                    m_tornBits = -1051583237

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 9, DumpStyle BYTE

Record Type = PRIMARY_RECORD         Record Attributes =                  Record Size = 9

Memory Dump @0x000000001580C060

0000000000000000:   00000800 e2040000 09†††††††††††††††††….â…        

DS_CONFIG @0x000000001580C060

cconfsz = 8                          cmajor = 9                           cminor = 0
crevision = 75                       cbootsource = 2                      crecinterval = 0
ccatalogupdates = 0                  cmbSrvMemMin = 0                     cmbSrvMemMax = 2147483647
cusrconnections = 0                  cnetworkpcksize = 4096               ckbIndexMem = 0
cfillfactor = 0                      ctapreten = 0                        cwritestatus = 0
cfgpriorityboost = 0x0               cfgexposeadvparm = 0x0               cfglogintime = 20
cfgpss = 0                           cfgpad = 4096                        cfgxdes = 16
cfgaffinitymask = 0                  cfgaffinitymask64 = 0                cfgIOAffinityMask = 0
cfgIOAffinity64Mask = 0              cfgbuf = 4362                        cfgdes = 0
cfglocks = 0                         cfgquerytime = 600                   cfgcursorthrsh = -1
cfgrmttimeout = 10                   cfg_dtc_rpcs = 0                     cclkrate = 31250
cfg_max_text_repl_size = 65536       cfgupddate = 41023                   cfgupdtime = 14252889
fRemoteAccess = 1                    cfgbufminfree = 331                  cnestedtriggers = 0x1
cdeflang = 0                         cfgTransformNoiseWords = 0x0         cfgPrecomputeRank = 0x0
crossdbownchain = 0                  cidCfg = 0x3400d008                  cidCfgOld = 0x3400d008
cfgCutoffYear = 2049                 cfgLangNeutralFT = 1033              maxworkthreads = 0
minworkthreads = 32                  minnetworkthreads = 32               threadtimeout = 15
connectsperthread = 0                cusroptions = 0                      exchcostthreshold = 5
maxdop = 0                           cpwdpolicyupgrade = 0x1              cfServerTriggerRecursion = 1
cfDisableResultsetsFromTriggers = 0  cfgPHConnectTimeout = 60             CLREnabled = 0
cfgmaxcrawlrange = 4                 ftSmallBufMin = 0                    ftSmallBufMax = 100
ftLargeBufMin = 0                    ftLargeBufMax = 100                  RemoteDacEnabled = 0
CommCritComplianceEnabled = 0        EkmEnabled = 0                       cUserInstanceTimeout = 0x3c
cfgEnableUserInstances = 0x1         m_BackupCompressionDefault = 0x0     FilestreamAccessLevel = 2
OptimizeForAdhocWorkloads = 0        cchecksum = 1250                    

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

You can see that the CONFIG block is stored in a special way as the record length DBCC PAGE reports is only 9 bytes, whereas the CONFIG block is several hundred bytes. When SQL Server starts, it opens a channel directly to master's MDF and reads the entire 8Kb of page 10 into memory and typecasts it into the in-memory CONFIG data structure. If the CONFIG block is damaged, the server won't start.

As you can see from the output, not everything in the CONFIG block is exposed or settable via sp_configure.

In the next post, I'll explain how to tell when sp_configure options were last updated.

Make sure you're backing up the master database!

2 thoughts on “Where are sp_configure settings stored? Another reason to backup master…

  1. Good to know, thanks Paul!

    Just for completeness, does CHECKDB check pages 4 and 5 (currently unused)? If they somehow got garbled would it show as corruption?

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.