Random Configuration Trap 001

Here is a funny little configuration anomaly to be aware of: the “allow updates” server configuration option is not only deprecated in SQL Server 2008 R2, but it could also break your application if not set to 0 (zero : FALSE).

In a nutshell, if “allow updates” is set to 1 (TRUE), then you cannot issue the RECONFIGURE statement. It will fail with:

Msg 5808, Level 16, State 1, Line 1
Ad hoc update to system catalogs is not supported.

But could that really break your application? Yes. For example, if you run a DBCC CHECKDB command in a routine or on-demand maintenance task, part of the CHECKDB automated process runs a RECONFIGURE. Net effect: FAIL.

The skeptical reader may ask: “if you can’t reconfigure when ‘allow updates’ is set to TRUE, how could ‘allow updates’ get changed from FALSE to TRUE in the first place?” Well, I’m glad you asked. Here’s how: first, run the following commands to check the current configuration and then configure the “allow updates” option:

-- Show advanced options, so we can see the setting on "allow updates"
EXECUTE sp_configure 'show advanced options', 1;
GO

-- Reconfigure the server to apply the change we just made
RECONFIGURE;
GO

-- Change the setting for "allow updates" to TRUE
EXECUTE sp_configure 'allow updates', 1;
GO

-- View the current configuration settings
--   Compare the "config value" to the "run value" for "allow updates"
EXECUTE sp_configure;
GO

-- If you want to test, reconfigure and see the error
RECONFIGURE;
GO

Then, in SSMS, right-click your server, select Properties, go to the Memory page, modify the “Minimum server memory (in MB)” property (or any property, in fact), and click ok.

Voilà; the “allow updates” change has been applied. Check the configuration and see:

-- Compare the "config value" to the "run value" for "allow updates"
EXECUTE sp_configure;
GO

And confirm that RECONFIGURE no longer works:

-- Reconfigure to see the failure message
RECONFIGURE;
GO

In the message section you should see the error detailed at the beginning of this post.

Since “allow updates” has been deprecated for a while now, in theory no one should be poking around and flicking that switch anyway. But, you know how the saying goes: “In theory there is no difference between theory and practice. In practice, there is.” (Incidentally, was that said by Yogi Berra or by van de Snepsheut? I lean toward the latter….)

Yes, while a careful reading of books online will tell you not to change “allow updates”, I’d still guess that, like most folks, I tend to experience these pitfalls in the wild before I read about them in BOL.

I’m intrepid that way. 😉

By the way, be sure to reset your “allow updates” back to FALSE. It’s not a big deal, though, because I’m sure you were working in a test environment, right?!

-- Change the setting for "allow updates" to FALSE
EXECUTE sp_configure 'allow updates', 0;
GO

-- Hide advanced options
EXECUTE sp_configure 'show advanced options', 0;
GO

-- Reconfigure the server to apply the changes
RECONFIGURE;
GO

Advertisements
Post a comment or leave a trackback: Trackback URL.

Comments

  • Mike Magnusson  On 13 Jun 2012 at 2047

    van de Snepsheut? The ax murderer? I will go with Yogi Berra’s version I guess (unless you are headed my way with an Ax).

  • lextonr  On 25 Jul 2012 at 1027

    If you’re running a version of SQL Server prior to 2008 R2 SP1, take a look at this: right-click a linked server, choose Properties, and then Script > Script Action to New Query Window. Notice anything strange? Yes, that’s right — SQL Server is attempting to set “allow updates” to true!? This, despite having deprecated the feature and warned developers not to use it. Very funny stuff. 😀 At least MS did fix the issue in 2008 R2 SP1, so no worries going forward.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: