{"id":494,"date":"2012-04-26T10:15:00","date_gmt":"2012-04-26T10:15:00","guid":{"rendered":"\/blogs\/paul\/post\/Where-are-sp_configure-settings-stored-Another-reason-to-backup-master.aspx"},"modified":"2017-04-13T09:50:38","modified_gmt":"2017-04-13T16:50:38","slug":"where-are-sp_configure-settings-stored-another-reason-to-backup-master","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/where-are-sp_configure-settings-stored-another-reason-to-backup-master\/","title":{"rendered":"Where are sp_configure settings stored? Another reason to backup master&#8230;"},"content":{"rendered":"<p>\n<font face=\"verdana,geneva\" size=\"2\">Earlier today I <a href=\"http:\/\/sqlmag.com\/blog\/my-master-database-really-corrupt\" target=\"_blank\">blogged on our SQL Server Pro magazine blog<\/a> about false-alarm corruptions you will *definitely* see if you restore a backup of the <font face=\"courier new,courier\">master<\/font> database as a user database and run <font face=\"courier new,courier\">DBCC CHECKDB<\/font> 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&#39;ll definitely get a shock the first time you see these false-alarms and think your real <font face=\"courier new,courier\">master<\/font> is corrupt!<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">One of the &#39;corruptions&#39; is because of a special page that only exists in the <font face=\"courier new,courier\">master<\/font> database &#8211; the <font face=\"courier new,courier\">CONFIG<\/font> block of the SQL Server instance. It&#39;s page number 10 in data file ID 1 of master. <font face=\"courier new,courier\">DBCC CHECKDB<\/font> flags this as a corruption if it&#39;s present in any other database apart from <font face=\"courier new,courier\">master<\/font>, as the page in <font face=\"courier new,courier\">master<\/font> is marked as allocated, but no object owns it. This is only allowed in <font face=\"courier new,courier\">master<\/font>. Similar allowances are made by <font face=\"courier new,courier\">DBCC CHECKDB<\/font> in every database for the boot page (page 9 in file 1) and for each data and log file&#39;s file header page (page 0 in each file). See <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-20-boot-pages-and-boot-page-corruption\/\" target=\"_blank\">here for a blog post about the boot page<\/a> and <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-21-file-header-pages-and-file-header-corruption\/\" target=\"_blank\">here for a blog post about file header pages<\/a>.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">What does this special page store? It stores all the <font face=\"courier new,courier\">sp_configure<\/font> options for the SQL Server instance. This is yet one more reason to make sure you regularly back up the <font face=\"courier new,courier\">master<\/font> database&nbsp;because if you lose <font face=\"courier new,courier\">master<\/font> then you lose all your configuration settings for that SQL Server instance.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">You can examine this page using <font face=\"courier new,courier\">DBCC PAGE<\/font> or the equally undocumented, but nowhere nearly as well known, <font face=\"courier new,courier\">DBCC CONFIG<\/font> command (they use the same code under the covers and both require trace flag 3604).<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Here&#39;s an example from one of my laptop instances:<\/font>\n<\/p>\n<blockquote>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">DBCC TRACEON (3604);<br \/>\n\tDBCC PAGE (&#39;master&#39;, 1, 10, 3);<br \/>\n\tGO<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">PAGE: (1:10)<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">BUFFER:<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">BUF @0x0000000080FC0AC0<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">bpage = 0x0000000080572000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; bhash = 0x0000000000000000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; bpageno = (1:10)<br \/>\n\tbdbid = 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; breferences = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; bcputicks = 264<br \/>\n\tbsampleCount = 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; bUse1 = 23062&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; bstat = 0xc00009<br \/>\n\tblog = 0x32159&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; bnext = 0x0000000000000000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">PAGE HEADER:<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">Page @0x0000000080572000<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">m_pageId = (1:10)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m_headerVersion = 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m_type = 14<br \/>\n\tm_typeFlagBits = 0x0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m_level = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m_flagBits = 0x200<br \/>\n\tm_objId (AllocUnitId.idObj) = 99&nbsp;&nbsp;&nbsp;&nbsp; m_indexId (AllocUnitId.idInd) = 0&nbsp;&nbsp;&nbsp; Metadata: AllocUnitId = 6488064<br \/>\n\tMetadata: PartitionId = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Metadata: IndexId = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Metadata: ObjectId = 99<br \/>\n\tm_prevPage = (0:0)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m_nextPage = (0:0)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; pminlen = 0<br \/>\n\tm_slotCnt = 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m_freeCnt = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m_freeData = 8190<br \/>\n\tm_reservedCnt = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m_lsn = (0:0:1)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m_xactReserved = 0<br \/>\n\tm_xdesId = (0:0)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m_ghostRecCnt = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m_tornBits = -1051583237<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">Allocation Status<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">GAM (1:2) = ALLOCATED&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SGAM (1:3) = NOT ALLOCATED&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br \/>\n\tPFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DIFF (1:6) = CHANGED<br \/>\n\tML (1:7) = NOT MIN_LOGGED&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">Slot 0, Offset 0x60, Length 9, DumpStyle BYTE<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">Record Type = PRIMARY_RECORD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Record Attributes =&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Record Size = 9<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">Memory Dump @0x000000001580C060<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">0000000000000000:&nbsp;&nbsp; 00000800 e2040000 09&dagger;&dagger;&dagger;&dagger;&dagger;&dagger;&dagger;&dagger;&dagger;&dagger;&dagger;&dagger;&dagger;&dagger;&dagger;&dagger;&dagger;&#8230;.&acirc;&#8230;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">DS_CONFIG @0x000000001580C060<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">cconfsz = 8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cmajor = 9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cminor = 0<br \/>\n\tcrevision = 75&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cbootsource = 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; crecinterval = 0<br \/>\n\tccatalogupdates = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cmbSrvMemMin = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cmbSrvMemMax = 2147483647<br \/>\n\tcusrconnections = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cnetworkpcksize = 4096&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ckbIndexMem = 0<br \/>\n\tcfillfactor = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ctapreten = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cwritestatus = 0<br \/>\n\tcfgpriorityboost = 0x0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfgexposeadvparm = 0x0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfglogintime = 20<br \/>\n\tcfgpss = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfgpad = 4096&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfgxdes = 16<br \/>\n\tcfgaffinitymask = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfgaffinitymask64 = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfgIOAffinityMask = 0<br \/>\n\tcfgIOAffinity64Mask = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfgbuf = 4362&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfgdes = 0<br \/>\n\tcfglocks = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfgquerytime = 600&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfgcursorthrsh = -1<br \/>\n\tcfgrmttimeout = 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfg_dtc_rpcs = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cclkrate = 31250<br \/>\n\tcfg_max_text_repl_size = 65536&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfgupddate = 41023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfgupdtime = 14252889<br \/>\n\tfRemoteAccess = 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfgbufminfree = 331&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cnestedtriggers = 0x1<br \/>\n\tcdeflang = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfgTransformNoiseWords = 0x0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfgPrecomputeRank = 0x0<br \/>\n\tcrossdbownchain = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cidCfg = 0x3400d008&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cidCfgOld = 0x3400d008<br \/>\n\tcfgCutoffYear = 2049&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfgLangNeutralFT = 1033&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; maxworkthreads = 0<br \/>\n\tminworkthreads = 32&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; minnetworkthreads = 32&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; threadtimeout = 15<br \/>\n\tconnectsperthread = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cusroptions = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; exchcostthreshold = 5<br \/>\n\tmaxdop = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cpwdpolicyupgrade = 0x1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfServerTriggerRecursion = 1<br \/>\n\tcfDisableResultsetsFromTriggers = 0&nbsp; cfgPHConnectTimeout = 60&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CLREnabled = 0<br \/>\n\tcfgmaxcrawlrange = 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ftSmallBufMin = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ftSmallBufMax = 100<br \/>\n\tftLargeBufMin = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ftLargeBufMax = 100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RemoteDacEnabled = 0<br \/>\n\tCommCritComplianceEnabled = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EkmEnabled = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cUserInstanceTimeout = 0x3c<br \/>\n\tcfgEnableUserInstances = 0x1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; m_BackupCompressionDefault = 0x0&nbsp;&nbsp;&nbsp;&nbsp; FilestreamAccessLevel = 2<br \/>\n\tOptimizeForAdhocWorkloads = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cchecksum = 1250&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">DBCC execution completed. If DBCC printed error messages, contact your system administrator.<br \/>\n\t<\/font>\n\t<\/p>\n<\/blockquote>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">You can see that the <font face=\"courier new,courier\">CONFIG<\/font> block is stored in a special way as the record length <font face=\"courier new,courier\">DBCC PAGE<\/font> reports is only 9 bytes, whereas the <font face=\"courier new,courier\">CONFIG<\/font> block is several hundred bytes. When SQL Server starts, it opens a channel directly to master&#39;s MDF and reads the entire 8Kb of page 10 into memory and typecasts it into the in-memory <font face=\"courier new,courier\">CONFIG<\/font> data structure. If the <font face=\"courier new,courier\">CONFIG block<\/font> is damaged, the server won&#39;t start.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">As you can see from the output, not everything in the <font face=\"courier new,courier\">CONFIG<\/font> block is exposed or settable via <font face=\"courier new,courier\">sp_configure<\/font>.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">In the <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-when-were-the-sp_configure-options-last-changed\/\" target=\"_blank\">next post<\/a>, I&#39;ll explain how to tell when <font face=\"courier new,courier\">sp_configure<\/font> options were last updated.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Make sure you&#39;re backing up the master database!<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\"><\/font><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,48,62],"tags":[],"class_list":["post-494","post","type-post","status-publish","format-standard","hentry","category-database-maintenance","category-inside-the-storage-engine","category-on-disk-structures"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Where are sp_configure settings stored? Another reason to backup master... - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/where-are-sp_configure-settings-stored-another-reason-to-backup-master\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Where are sp_configure settings stored? Another reason to backup master... - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"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 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/where-are-sp_configure-settings-stored-another-reason-to-backup-master\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2012-04-26T10:15:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:50:38+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"10 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/where-are-sp_configure-settings-stored-another-reason-to-backup-master\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/where-are-sp_configure-settings-stored-another-reason-to-backup-master\/\",\"name\":\"Where are sp_configure settings stored? Another reason to backup master... - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2012-04-26T10:15:00+00:00\",\"dateModified\":\"2017-04-13T16:50:38+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/where-are-sp_configure-settings-stored-another-reason-to-backup-master\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/where-are-sp_configure-settings-stored-another-reason-to-backup-master\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/where-are-sp_configure-settings-stored-another-reason-to-backup-master\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Where are sp_configure settings stored? Another reason to backup master&#8230;\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Where are sp_configure settings stored? Another reason to backup master... - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/where-are-sp_configure-settings-stored-another-reason-to-backup-master\/","og_locale":"en_US","og_type":"article","og_title":"Where are sp_configure settings stored? Another reason to backup master... - Paul S. Randal","og_description":"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 [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/where-are-sp_configure-settings-stored-another-reason-to-backup-master\/","og_site_name":"Paul S. Randal","article_published_time":"2012-04-26T10:15:00+00:00","article_modified_time":"2017-04-13T16:50:38+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/where-are-sp_configure-settings-stored-another-reason-to-backup-master\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/where-are-sp_configure-settings-stored-another-reason-to-backup-master\/","name":"Where are sp_configure settings stored? Another reason to backup master... - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2012-04-26T10:15:00+00:00","dateModified":"2017-04-13T16:50:38+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/where-are-sp_configure-settings-stored-another-reason-to-backup-master\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/where-are-sp_configure-settings-stored-another-reason-to-backup-master\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/where-are-sp_configure-settings-stored-another-reason-to-backup-master\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Where are sp_configure settings stored? Another reason to backup master&#8230;"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/494","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=494"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/494\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=494"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=494"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=494"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}