New script: When were the sp_configure options last changed?

In my previous post I explained how the sp_configure settings are stored in a special page in the master database called the CONFIG block. Sometimes you might want to know when these were last changed if error logs are not available for some reason (as sp_configure changes are noted in the error log) or someone tried to cover his/her tracks by editing them. You can figure this out by dumping the CONFIG block directly using DBCC PAGE or DBCC CONFIG.

Here's the CONFIG block for one of the instances on my laptop:

DBCC TRACEON (3604);
DBCC CONFIG;
GO

CONFIG:

DS_CONFIG @0x00000000043A3060

cconfsz = 8                          cmajor = 9                           cminor = 0
crevision = 76                       cbootsource = 2                      crecinterval = 0
ccatalogupdates = 0                  cmbSrvMemMin = 16                    cmbSrvMemMax = 2147483647
cusrconnections = 0                  cnetworkpcksize = 4096               ckbIndexMem = 0
cfillfactor = 0                      ctapreten = 0                        cwritestatus = 0
cfgpriorityboost = 0x0               cfgexposeadvparm = 0x1               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 = 1                           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 = 1191                    
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The two highlighted fields are the time the sp_configure options were last updated. The cfgupddate is the day that sp_configure was last updated and the cfgupdtime is the time on that day is was last updated. Here's how to interpret them:

  • cfgupdtime is the number of 3.3ms intervals since midnight. 14252889 x 3.3ms = 47034533.7ms = 47034s. 47036 div 3600 (secs/hour) = 13 hours. And (47036 mod 3600) div 60 (mins/hour) = 3 mins. And (47036 mod 3600) – (3 x 60) = 56 secs. I last updated my sp_configure settings at 13:03:56 when I tested it by setting MAXDOP to 1.
  • cfgupddate is more complex. It's the number of days since January 1st, 1900. Doing a raw 41023 / 365 = 112.391blah. So 112 years, leaving 41023 – (112 * 365) = 143 days over. but we also have to account for the extra day in all the leap years since 1900. 1900 itself isn't a leap year, but 2000 is, plus 24 between 1900 and 2000, and 2 since 2000, not including this year. So we have 143 – 1 – 24 – 2 = 116 days. 116 – 31 (Jan) – 29 (Feb) – 31 (Mar) = 25. But it's the number of days *since* January 1st, 1900 so we add 1 and we get 26 – and today's the 26th of April.

I last changed the sp_configure options at 13:03:56 on April 26, 2012. Its trivial to come up with T-SQL code to do all of this, using the WITH TABLERESULTS option of DBCC PAGE.

Note the time of the last sp_configure change is only flushed to the CONFIG blocck on disk when the server restarts, is recollated, or some other major change occurs. This means that if you make a change, the time returned by the code will not change. You can prove this to your self by making a change, waiting an hour, rebooting, and running the script. The time returned will be the change time, not the reboot time.

[Edit 4/30/12: I fixed a small arithmetic bug in my milliseconds calculation thanks to Nils Gustav Stråbø.]

You can download the code from SQLskillsSPConfigureChanged.zip (1.50 kb)

If you want to figue out *what* changed and you don't have error logs available, you'll have to restore previous backups of master (you're backing it up every night, right?) and compare the CONFIG blocks.

Hope this is useful (or at least interesting!) to some of you. Better go set MAXDOP back to zero before I forget…

The code to do this is below (I'm sure someone will comment on a cleverer way to do some of this):

/*============================================================================
   File: SQLskillsSPConfigureChanged.sql

   Summary: This script reports the time that sp_configure options were
   last changed

   SQL Server Versions:
         2005 RTM onwards
——————————————————————————
  Written by Paul S. Randal, SQLskills.com
 
  (c) 2011, SQLskills.com. All rights reserved.

  For more scripts and sample code, check out
   
http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.
 
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/


SET NOCOUNT ON;

— Create the temp table

IF EXISTS (SELECT * FROM msdb.sys.objects WHERE NAME = 'SQLskillsDBCCPage')
DROP TABLE msdb.dbo.SQLskillsDBCCPage;

CREATE TABLE msdb.dbo.SQLskillsDBCCPage (
    [ParentObject] VARCHAR (100),
    [Object]       VARCHAR (100),
    [Field]        VARCHAR (100),
    [VALUE]        VARCHAR (100));

DECLARE @hours   INT;
DECLARE @minutes  INT;
DECLARE @seconds  INT;
DECLARE @milliseconds BIGINT;
DECLARE @LastUpdateTime DATETIME;
DECLARE @upddate  INT;
DECLARE @updtime  BIGINT;
DECLARE @dbccPageString VARCHAR (200);

— Build the dynamic SQL

SELECT @dbccPageString = 'DBCC PAGE (master, 1, 10, 3) WITH TABLERESULTS, NO_INFOMSGS';

— Empty out the temp table and insert into it again

INSERT INTO msdb.dbo.SQLskillsDBCCPage EXEC (@dbccPageString);

SELECT @updtime = [VALUE] FROM msdb.dbo.SQLskillsDBCCPage
WHERE [Field] = 'cfgupdtime';
SELECT @upddate = [VALUE] FROM msdb.dbo.SQLskillsDBCCPage
WHERE [Field] = 'cfgupddate';

— Convert updtime to seconds
SELECT @milliseconds = CONVERT (INT, CONVERT (FLOAT, @updtime) * (3 + 1.0/3))
SELECT @updtime = @milliseconds / 1000;

— Pull out hours, minutes, seconds, milliseconds
SELECT @hours = @updtime / 3600;

SELECT @minutes = (@updtime % 3600) / 60;

SELECT @seconds = @updtime – (@hours * 3600) – (@minutes * 60);

— Calculate number of milliseconds
SELECT @milliseconds = @milliseconds –
 @seconds * 1000 –
 @minutes * 60 * 1000 –
 @hours * 3600 * 1000;
 
— No messy conversion code required for the date as SQL Server can do it for us
SELECT @LastUpdateTime = DATEADD (DAY, @upddate, '1900-01-01');

— And add in the hours, minutes, seconds, and milliseconds
— There are nicer functions to do this but they don't work in 2005/2008
SELECT @LastUpdateTime = DATEADD (HOUR, @hours, @LastUpdateTime);
SELECT @LastUpdateTime = DATEADD (MINUTE, @minutes, @LastUpdateTime);
SELECT @LastUpdateTime = DATEADD (SECOND, @seconds, @LastUpdateTime);
SELECT @LastUpdateTime = DATEADD (MILLISECOND, @milliseconds, @LastUpdateTime);

SELECT @LastUpdateTime AS 'sp_configure options last updated';

— Clean up

DROP TABLE msdb.dbo.SQLskillsDBCCPage;
GO

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!

Photos from the Mojave Desert

(Sitting in the back of the class while Kimberly's teaching and I'm cranking through my massive backlog of blog posts.)

  Inside a lave tube with light streaming through ceiling holes and through the dust.

After SQL Connections at the start of April in Las Vegas we drove across to San Diego for a friend's birthday party. We decided to bring all our camera gear along and drive through the Mojave National Preserve, renting a 4×4 so we could go off into the dirt roads where necessary.

It's a pretty cool place – vast, of course – and one of the few times I've stood and had tens of miles of flat ground around me in all directions with nothing except scrub bushes and a small road, and deep, deep blue sky overhead.

There's a good PDF map of the Preserve on the National Park Service website here and I recommend perusing their site before going.

We entered the Preserve just off I-15 west of Vegas at a place call Nipton and headed down to the information center at Hole-In-The-Wall. This was a 20 mile drive on a dirt road, great fun in the 4×4 with wheel ruts throwing the SUV around (I love driving off-road, and fast if possible :-). Lots of bird life around and some ground squirrels too. At Hole-In-The-Wall (named after the more famous bandit hide-out in Wyoming) there are cool rock formations and rock-climbing routes. We drove back up to the main road and headed along to Kelso Depot, which used to be a major locomotive depot for the Union Pacific Railroad during WWII. It provided extra locomotives for the ore trains heading east – now it's a really small town, but has a great little restaurant in the information center. Try the chili – it's excellent. We then headed back north to I-15 through the Cinder Cone Lava Beds – lots of giant lava flows and cinder cones from volcanic vents. Turning off onto a tiny road (Aiken Mine Road) we headed out into the lava to find a lava tube that you can get down into and explore – this was fabulous and the highlight of the day for sure. It's marked on the map I linked to above.

After the party, the next day we drove down to San Diego airport and stopped at the flying park north of the city. This is a flying club and launch area set on high sea cliffs over the Pacific with excellent thermals for hang-gliding and parascending. At one point I counted over 30 flyers in the air. If we'd had more time we'd have signed up for some tandem flights.

All in all a great road trip, albeit a little short. We're going to make a point of driving to more places in future – flying makes you miss so much!

All photos were taken with a Canon 5D Mark II with a 24-105mm f/4L lens shooting fully manual. It took me a while to figure out, but once you have the shutter speed/focal-length/'film' speed triangle worked out in your head, you'll never go back from fully manual control of your camera.

Click on the photos for a 1024×768 version. Full-size versions available on request.

Below: Preserve entrance sign with scrub to the horizon and then mountains; abandoned cabin in a small homestead in the hamlet of Cima.

 

Below: Kimberly in front of the rocks at Hole-In-The-Wall; a cactus about to flower – we were just a bit too early for the flowering to be in full swing.

 

Below: the main cavern inside the lave tube. The light was just incredible, with powerful sun rays coming through the dust inside the cavern. It kind of reminded me of the scene in Raiders of the Lost Ark where Indy goes down into a chamber in the Egyptian city and a ray of light comes through a skylight, through a gem he's holding and lights up a location on a map of the city. The second photo is of a smaller skylight with a really intense beam of light. Just stunning.

 

Below: the blocked-up entrance to an old mineshaft – maybe part of the old abandoned Aiken Mine?

 

Below: flyers over the Pacific just north of San Diego. The second photo has 8 flyers in!