{"id":493,"date":"2012-04-26T11:30:00","date_gmt":"2012-04-26T11:30:00","guid":{"rendered":"\/blogs\/paul\/post\/When-were-the-sp_configure-options-last-changed.aspx"},"modified":"2017-04-13T09:49:48","modified_gmt":"2017-04-13T16:49:48","slug":"new-script-when-were-the-sp_configure-options-last-changed","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-when-were-the-sp_configure-options-last-changed\/","title":{"rendered":"New script: When were the sp_configure options last changed?"},"content":{"rendered":"<p>\n<font face=\"verdana,geneva\" size=\"2\">In my <\/font><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/where-are-sp_configure-settings-stored-another-reason-to-backup-master\/\" target=\"_blank\"><font face=\"verdana,geneva\" size=\"2\">previous post<\/font><\/a><font face=\"verdana,geneva\" size=\"2\"> I explained how the <font face=\"courier new,courier\">sp_configure<\/font> settings are stored in a special page in the <font face=\"courier new,courier\">master<\/font> database called the <font face=\"courier new,courier\">CONFIG<\/font> block. Sometimes you might want to know when these were last changed if error logs are not available for some reason (as <font face=\"courier new,courier\">sp_configure<\/font> 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 <font face=\"courier new,courier\">CONFIG<\/font> block directly using <font face=\"courier new,courier\">DBCC PAGE<\/font> or <font face=\"courier new,courier\">DBCC CONFIG<\/font>. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Here&#39;s the&nbsp;<font face=\"courier new,courier\">CONFIG<\/font> block for one of the instances on my laptop: <\/font>\n<\/p>\n<blockquote>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">DBCC TRACEON (3604);<br \/>\n\tDBCC CONFIG;<br \/>\n\tGO <\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">CONFIG: <\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">DS_CONFIG @0x00000000043A3060 <\/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 = 76&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 = 16&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 = 0x1&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; <\/font><font face=\"verdana,geneva\"><font size=\"2\"><font face=\"courier new,courier\"><strong>cfgupddate = 41023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cfgupdtime = 14252889<br \/>\n\t<\/strong>fRemoteAccess = 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 = 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; 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 = 1191&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br \/>\n\tDBCC execution completed. If DBCC printed error messages, contact your system administrator.<br \/>\n\t<\/font><\/font><\/font>\n\t<\/p>\n<\/blockquote>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">The two highlighted fields are the time the <font face=\"courier new,courier\">sp_configure<\/font> options were last updated. The <font face=\"courier new,courier\">cfgupddate<\/font> is the day that <font face=\"courier new,courier\">sp_configure<\/font> was last updated and the <font face=\"courier new,courier\">cfgupdtime<\/font> is the time on that day is was last updated. Here&#39;s how to interpret them: <\/font>\n<\/p>\n<ul>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\"><font face=\"courier new,courier\">cfgupdtime<\/font> 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) &#8211; (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. <\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\"><font face=\"courier new,courier\">cfgupddate<\/font> is more complex. It&#39;s the number of days since January 1st, 1900. Doing a raw 41023 \/ 365 = 112.391blah. So 112 years, leaving 41023 &#8211; (112 * 365)&nbsp;= 143 days over. but we also have to account for the extra day in all the leap years since 1900. 1900 itself isn&#39;t a leap year, but 2000 is, plus 24 between 1900 and 2000, and&nbsp;2 since 2000, not including this year. So we have 143 &#8211; 1 &#8211; 24 &#8211;&nbsp;2 = 116 days. 116 &#8211; 31 (Jan) &#8211; 29 (Feb) &#8211; 31 (Mar) = 25. But it&#39;s the number of days *since* January 1st, 1900 so we add 1 and we get 26 &#8211; and today&#39;s the 26th of April. <\/font>\n\t<\/div>\n<\/li>\n<\/ul>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">I last changed the <font face=\"courier new,courier\">sp_configure<\/font> 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 <font face=\"courier new,courier\">WITH TABLERESULTS<\/font> option of <font face=\"courier new,courier\">DBCC PAGE<\/font>.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Note&nbsp;the time of the last <font face=\"courier new,courier\">sp_configure<\/font> change is only flushed to the <font face=\"courier new,courier\">CONFIG<\/font> 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.<\/font>\n<\/p>\n<p>\n<font size=\"2\">[Edit 4\/30\/12: I fixed a small arithmetic bug in my milliseconds calculation thanks to Nils Gustav Str&aring;b&oslash;.]<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">You can download the code from <\/font><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2012\/4\/sqlskillsspconfigurechanged.zip\"><font face=\"verdana,geneva\" size=\"2\">SQLskillsSPConfigureChanged.zip (1.50 kb)<\/font><\/a>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">If you want to figue out *what* changed and you don&#39;t have error logs available, you&#39;ll have to restore previous backups of master (you&#39;re backing it up every night, right?) and compare the <font face=\"courier new,courier\">CONFIG<\/font> blocks. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Hope this is useful (or at least interesting!) to some of you. Better go set <font face=\"courier new,courier\">MAXDOP<\/font> back to zero before I forget&#8230;<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">The code to do this is below (I&#39;m sure someone will comment on a cleverer way to do some of this):<\/font>\n<\/p>\n<blockquote>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">\/*============================================================================<br \/>\n\t&nbsp;&nbsp; File: SQLskillsSPConfigureChanged.sql<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">&nbsp;&nbsp; Summary: This script reports the time that sp_configure options were<br \/>\n\t&nbsp;&nbsp;&nbsp;last changed<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">&nbsp;&nbsp; SQL Server Versions:<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2005 RTM onwards<br \/>\n\t&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;<br \/>\n\t&nbsp; Written by Paul S. Randal, SQLskills.com<br \/>\n\t&nbsp;<br \/>\n\t&nbsp; (c) 2011, SQLskills.com. All rights reserved.<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">&nbsp; For more scripts and sample code, check out <br \/>\n\t&nbsp;&nbsp;&nbsp; <\/font><a href=\"https:\/\/www.sqlskills.com\/\"><font face=\"courier new,courier\" size=\"2\">http:\/\/www.SQLskills.com<\/font><\/a>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">&nbsp; You may alter this code for your own *non-commercial* purposes. You may<br \/>\n\t&nbsp; republish altered code as long as you include this copyright and give due<br \/>\n\t&nbsp; credit, but you must obtain prior permission before blogging this code.<br \/>\n\t&nbsp; <br \/>\n\t&nbsp; THIS CODE AND INFORMATION ARE PROVIDED &quot;AS IS&quot; WITHOUT WARRANTY OF <br \/>\n\t&nbsp; ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED <br \/>\n\t&nbsp; TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND\/OR FITNESS FOR A<br \/>\n\t&nbsp; PARTICULAR PURPOSE.<br \/>\n\t============================================================================*\/<\/font>\n\t<\/p>\n<p>\n\t<br \/>\n\t<font face=\"courier new,courier\" size=\"2\">SET NOCOUNT ON;<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">&#8212; Create the temp table<br \/>\n\t&#8212;<br \/>\n\tIF EXISTS (SELECT * FROM msdb.sys.objects WHERE NAME = &#39;SQLskillsDBCCPage&#39;)<br \/>\n\tDROP TABLE msdb.dbo.SQLskillsDBCCPage;<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">CREATE TABLE msdb.dbo.SQLskillsDBCCPage (<br \/>\n\t&nbsp;&nbsp;&nbsp; [ParentObject] VARCHAR (100),<br \/>\n\t&nbsp;&nbsp;&nbsp; [Object]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR (100),<br \/>\n\t&nbsp;&nbsp;&nbsp; [Field]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR (100),<br \/>\n\t&nbsp;&nbsp;&nbsp; [VALUE]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR (100)); <\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">DECLARE @hours&nbsp;&nbsp;&nbsp;INT;<br \/>\n\tDECLARE @minutes&nbsp;&nbsp;INT;<br \/>\n\tDECLARE @seconds&nbsp;&nbsp;INT;<br \/>\n\tDECLARE @milliseconds&nbsp;BIGINT;<br \/>\n\tDECLARE @LastUpdateTime&nbsp;DATETIME;<br \/>\n\tDECLARE @upddate&nbsp;&nbsp;INT;<br \/>\n\tDECLARE @updtime&nbsp;&nbsp;BIGINT;<br \/>\n\tDECLARE @dbccPageString VARCHAR (200);<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">&#8212; Build the dynamic SQL<br \/>\n\t&#8212;<br \/>\n\tSELECT @dbccPageString = &#39;DBCC PAGE (master, 1, 10, 3) WITH TABLERESULTS, NO_INFOMSGS&#39;;<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">&#8212; Empty out the temp table and insert into it again<br \/>\n\t&#8212;<br \/>\n\tINSERT INTO msdb.dbo.SQLskillsDBCCPage EXEC (@dbccPageString);<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">SELECT @updtime = [VALUE] FROM msdb.dbo.SQLskillsDBCCPage<br \/>\n\tWHERE [Field] = &#39;cfgupdtime&#39;;<br \/>\n\tSELECT @upddate = [VALUE] FROM msdb.dbo.SQLskillsDBCCPage<br \/>\n\tWHERE [Field] = &#39;cfgupddate&#39;;<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">&#8212; Convert updtime to seconds<br \/>\n\tSELECT @milliseconds = CONVERT (INT, CONVERT (FLOAT, @updtime) * (3 + 1.0\/3))<br \/>\n\tSELECT @updtime = @milliseconds \/ 1000;<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">&#8212; Pull out hours, minutes, seconds, milliseconds<br \/>\n\tSELECT @hours = @updtime \/ 3600;<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">SELECT @minutes = (@updtime % 3600) \/ 60;<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">SELECT @seconds = @updtime &#8211; (@hours * 3600) &#8211; (@minutes * 60);<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">&#8212; Calculate number of milliseconds<br \/>\n\tSELECT @milliseconds = @milliseconds &#8211;<br \/>\n\t&nbsp;@seconds * 1000 &#8211;<br \/>\n\t&nbsp;@minutes * 60 * 1000 &#8211;<br \/>\n\t&nbsp;@hours * 3600 * 1000;<br \/>\n\t&nbsp;<br \/>\n\t&#8212; No messy conversion code required for the date as SQL Server can do it for us<br \/>\n\tSELECT @LastUpdateTime = DATEADD (DAY, @upddate, &#39;1900-01-01&#39;);<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">&#8212; And add in the hours, minutes, seconds, and milliseconds<br \/>\n\t&#8212; There are nicer functions to do this but they don&#39;t work in 2005\/2008<br \/>\n\tSELECT @LastUpdateTime = DATEADD (HOUR, @hours, @LastUpdateTime);<br \/>\n\tSELECT @LastUpdateTime = DATEADD (MINUTE, @minutes, @LastUpdateTime);<br \/>\n\tSELECT @LastUpdateTime = DATEADD (SECOND, @seconds, @LastUpdateTime);<br \/>\n\tSELECT @LastUpdateTime = DATEADD (MILLISECOND, @milliseconds, @LastUpdateTime);<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">SELECT @LastUpdateTime AS &#39;sp_configure options last updated&#39;;<\/font>\n\t<\/p>\n<p>\n\t<font face=\"courier new,courier\" size=\"2\">&#8212; Clean up<br \/>\n\t&#8212;<br \/>\n\tDROP TABLE msdb.dbo.SQLskillsDBCCPage;<br \/>\n\tGO<br \/>\n\t<\/font>\n\t<\/p>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>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 [&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,38,48,62,100],"tags":[],"class_list":["post-493","post","type-post","status-publish","format-standard","hentry","category-database-maintenance","category-example-scripts","category-inside-the-storage-engine","category-on-disk-structures","category-undocumented-commands"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>New script: When were the sp_configure options last changed? - 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\/new-script-when-were-the-sp_configure-options-last-changed\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"New script: When were the sp_configure options last changed? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"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 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-when-were-the-sp_configure-options-last-changed\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2012-04-26T11:30:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:49:48+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\/new-script-when-were-the-sp_configure-options-last-changed\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-when-were-the-sp_configure-options-last-changed\/\",\"name\":\"New script: When were the sp_configure options last changed? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2012-04-26T11:30:00+00:00\",\"dateModified\":\"2017-04-13T16:49:48+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-when-were-the-sp_configure-options-last-changed\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-when-were-the-sp_configure-options-last-changed\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-when-were-the-sp_configure-options-last-changed\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"New script: When were the sp_configure options last changed?\"}]},{\"@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":"New script: When were the sp_configure options last changed? - 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\/new-script-when-were-the-sp_configure-options-last-changed\/","og_locale":"en_US","og_type":"article","og_title":"New script: When were the sp_configure options last changed? - Paul S. Randal","og_description":"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 [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-when-were-the-sp_configure-options-last-changed\/","og_site_name":"Paul S. Randal","article_published_time":"2012-04-26T11:30:00+00:00","article_modified_time":"2017-04-13T16:49:48+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\/new-script-when-were-the-sp_configure-options-last-changed\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-when-were-the-sp_configure-options-last-changed\/","name":"New script: When were the sp_configure options last changed? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2012-04-26T11:30:00+00:00","dateModified":"2017-04-13T16:49:48+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-when-were-the-sp_configure-options-last-changed\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-when-were-the-sp_configure-options-last-changed\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-when-were-the-sp_configure-options-last-changed\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"New script: When were the sp_configure options last changed?"}]},{"@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\/493","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=493"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/493\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=493"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=493"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=493"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}