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

13 thoughts on “New script: When were the sp_configure options last changed?

  1. Paul,

    As usual, thanks for the post. I have two questions. I ran the query against all of the instances in my dev environment it looks like the update time is changed when the instance restarts. Is this the case? Also, I flipped the Show Advanced Options config setting on one of the instances to look at the settings and this didn’t update the time. Looking at the output of the CONFIG page, I don’t see a value that corresponds to Show Advanced Options. Am I missing something?

    Thanks again,
    Frank

  2. Hey Frank – showing advanced options is the cfgexposeadvparm field. The last update time is correct, but it’s only flushed to the CONFIG block on disk when the server restarts, is recollated, or other major changes occur.

  3. Paul,

    I figured out the cfgexposeadvparm answer right after hitting send. Must try harder next time. :)
    What would the other major changes be?

    Thanks,
    Frank

  4. Paul,

    You are making the query more complicated than it has to be. You can replace everything between "Convert updtime to seconds" and "Clean up" with this.

    Select DateAdd(Day, @upddate, DateAdd(millisecond, @updtime * 3.3, 0)) ‘sp_configure options last updated’;

  5. Nice one on SQL Server internals Paul (as always).

    Found a small bug in your calculation (I think). The time part is not number of 3.3ms since midnight, but number of 3 1/3 ms.

    Change the line:
    SELECT @milliseconds = CONVERT (INT, CONVERT (FLOAT, @updtime) * 3.3)
    to:
    SELECT @milliseconds = CONVERT (INT, CONVERT (FLOAT, @updtime) * (3+1./3))

    You can also skip the hour, minute, second and millisecond caluclations and cast it (almost) to a datetime like this:
    declare @datetime bigint

    select @datetime=@upddate*4294967296+@updtime –ROL the date part 32 bits and add the time part
    select cast(cast(@datetime as binary(8)) as datetime)

    I’ve tested it on a development server (reconfigured and restarted the SQL Server service). Your code produced a result that was off by a few minutes (depending on the time of day).

  6. Hi Paul,

    Now it got worse :-)
    You forgot forgot the "3+" in "3+1.0/3", so now each "tick" in the time part is 0.333ms instead of 3.333ms.
    SELECT @milliseconds = CONVERT (INT, CONVERT (FLOAT, @updtime) * (1.0/3))

    Should be
    SELECT @milliseconds = CONVERT (INT, CONVERT (FLOAT, @updtime) * (3+1.0/3))
    or alternatively
    SELECT @milliseconds = CONVERT (INT, CONVERT (FLOAT, @updtime) * (10.0/3))

    Best regards,
    Nils

  7. Also want to say a big thank you for the amazing knowledge that you and your colleagues share with the SQL Server community.

    Regards,
    Nils

  8. Hi,
    i know this is an old post, but found it while trying to find a way to identify changes to the configuration.
    have one question:
    is the information saved in the special page not the same as what is saved in sys.configurations ?

    regards

    Peter

      1. I have been playing around a little with this.

        You write:
        ‘Note the time of the last sp_configure change is only flushed to the CONFIG block on disk when the server restarts, is recollated, or some other major change occurs’

        But this is also the case for the configuration values themselves is it not ?

        I may be misunderstanding something but my changes to the configuration is only persisted in the block after one of the said events.

        This means that if we have a 24/7 system i can not from this block see if anything changed since the last restart, which hopefully is weeks ago.

        As my original task was to find a way to figure out what was changed and who did it, i am looking at implementing this solution instead: https://www.mssqltips.com/sqlservertip/2364/capturing-and-alerting-on-sql-server-configuration-changes/

        Regards
        Peter

Leave a Reply

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

Other articles

Some thoughts on courage

(This is also the Ponderings – editorial – in today’s SQLskills newsletter.) I want to start out this post by sincerely thanking everyone who emailed

Explore

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.