Search

Maintenace plan integrity checks fail with "Alter Failed for server xxx" by ACALVETT

Active

5
0
Sign in
 to vote
Type: Bug
ID: 126163
Opened: 2/3/2006 8:16:57 AM
Access Restriction: Public
4
Workaround(s)
6
User(s) can reproduce this bug
My maintenance plan integrity check task was failing with "Alter failed for server XXX".

After tracing i found the maintenance plan executes sp_configure 'USER OPTIONS',xxxx followed by RECONFIGURE.
The reconfigure statement then generates an error causing the plan to fail. This was because the server configuration option "Allow Updates" was set to 1. Changed the setting back to 0 and the reconfigure statement started to work again!

BOL states the "allow updates" setting has no effect which is clearly not true as it causes this bug.
Details (expand)
Product Language
English
Version
SQL Server 2005 - Enterprise Edition (X64)
Category
SQL Engine
Operating System
Windows Server 2003
Operating System Language
US English
Steps to Reproduce
1) Create a maintenance plan task to do integrity checks on all databases
2) execute sp_configure 'allow updates', 1 reconfigure with override in query analyser
3) Run the maintenance plan
Actual Results
Maintenance plan fails with Alter failed for Server 'lonsql2005x64'.
Expected Results
1) Why is the maintenance plan issuing sp_configure 'user options'. It should not be even attempting to change this server setting although it does seem to preserve the current value it is unnecessary
2) RECONFIGURE statement should work even if allow updates is enabled
3) BOL states allow updates has no effect. It clearly does.
Platform
 
File Attachments
0 attachments
Sign in to post a comment.
Posted by sam_squarewave on 6/1/2009 at 3:49 PM
And the workaround is what?

Write your own script?
Posted by DBA_GaryC on 4/9/2009 at 3:13 AM
Thanks to adey11 as this solved the issue for us also!

Very random but works now so thanks - please Microsoft fix this one!

Gary.
Posted by Adey11 on 3/29/2009 at 8:15 PM
Check the "Automatically set I/O affinity mask for all processors" in your server properties. We had unselected this to reduce processors for licensing, and the maintenance plan failed the following weekend and since. I reselected thsi and the plan started working. Still sounds like a bug Microsoft should fix as a plan shouldn't fail for this reason.
Posted by SQL akuten on 2/5/2009 at 7:29 AM
I have same problem, though "Changed the setting back to 0 and the reconfigure statement started to work again!" is not true in my case. The running value for user options is 0 already but the job still tries to run "EXEC sys.sp_configure N'user options', 0 RECONFIGURE" with error 5808.
Posted by Microsoft on 10/16/2007 at 11:22 AM
Hello,
The Setting Server Configuration Options" topic in Katmai BOL has been corrected so this bug has been closed.

Thank you again for your feedback.
Regards,
Laurel
Posted by Catadmin on 11/30/2006 at 5:58 AM
I am having this exact same problem, but I do NOT have Allow Updates configured. It keeps failing because it cannot Alter Server, and is running the sp_configure 'user options' command. I have no user options chosen in the Server Properties, except Allow Remote Connections to this Server.

Please help. Nothing I do will make my Maintenance Plan work and I only have 2 steps in it. The integrity check and a notification on failure task. I've run DBCC CheckDB against my databases and it works fine with no errors, but I cannot run the Integrity Check successfully against any database on my system regardless of how many (or few) databases I've chosen.

Posted by Microsoft on 2/27/2006 at 3:16 PM
The SQL Server BOL topic "Setting Server Configuration Options" for allow updates now says: "Obsolete. Do not use. Will caus an error during reconfigure." And the BOL topic for the allow updates Option now says: Changing the allow updates option will cause the RECONFIGURE statement to fail. Changes to the allow updates option should be removed from all scripts." Thank you for your valuable feedback to make BOL better.
Posted by Microsoft on 2/27/2006 at 2:52 PM
The SQL Server BOL topic "Setting Server Configuration Options" for allow updates now says: "Obsolete. Do not use. Will caus an error during reconfigure." And the BOL topic for the allow updates Option now says: Changing the allow updates option will cause the RECONFIGURE statement to fail. Changes to the allow updates option should be removed from all scripts." Thank you for your valuable feedback to make BOL better.
Posted by Microsoft on 2/27/2006 at 11:16 AM
Books Online regarding allow updates was in error. Made the following changes.
Topic Setting Server Configuration Options, for allow updates now says: Obsolete. Do not use. Will cause an error during reconfigure.
Topic allow updates Option now says: Changing the allow updates option will cause the RECONFIGURE statement to fail. Changes to the allow updates option should be removed from all scripts.
Posted by ACALVETT on 2/7/2006 at 9:37 AM
Thank you both for the feedback.

In relation to the feedback from Sameer Verkhedkar, i understand it is no longer possible to update the system tables and we will no longer be using the allow updates option.

Will Books Online be updated? Currently it states the option has no effect and whilst its true that it will not allow updates to catalogues it does change the behaviour of the reconfigure statement. When i was trouble shooting our issue i found this statement in BOL misleading.

Regards

Andrew
Posted by Microsoft on 2/6/2006 at 5:40 PM
Thank you for reporting this problem. We are going to consider fixing this problem in the next version of Management Studio.

Ciprian Gerea
Posted by Microsoft on 2/3/2006 at 6:52 PM
Hello,

The "allow updates" option does not have its intended effect in SQL2005, i.e. you cannot update the system tables directly even when this option is ON.

However, you will still get the error (5808) when RECONFIGURE is called after turning on this option. This is the same behavior as SQL2000. This option was meant to take effect with
RECONFIGURE WITH OVERRIDE only. The same error behavior as SQL2000 has been preserved in SQL2005 although turning on the option does not have any effect on the ability to update system tables (which is
always disallowed in SQL2005).

It is recommended that you not use this option anymore and remove it from your scripts.

I'm forwarding your question regarding the Maintenance Plans to get it answered by the appropriate people.

Thank you for your feedback.

Sameer Verkhedkar
[MSFT]
Sign in to post a workaround.
Posted by Aneesh Ret on 4/4/2013 at 9:47 AM
If you set a value greater thna 60 for 'recovery interval (min)' , the same error can happen

http://sequelserver.blogspot.ca/2013/04/sql-server-2008-maintenance-plan-error.html
Posted by Matt Man on 6/29/2012 at 4:11 AM
The following script fixed it for me:
SP_CONFIGURE 'ALLOW UPDATES',0
GO
RECONFIGURE
GO
Posted by gtsdba on 5/14/2011 at 4:44 PM
fixed by setting allow updates to 0. Only getting this problem on a 32 bit instance.
Posted by Andreev 8389 on 6/3/2010 at 12:27 PM
2 Methods:
1 - Disable AWE
2 - Use GPO to grant "Lock Pages in Memory" for user, running SQL Server.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement