locked
SQL Server 2008: Maintenance Plan error - 'Alter failed for Server...' ...sp_configure? RRS feed

  • Question


  • Hello tried setting up a Maintenance Plan and got the above error...researched around the web and saw some mentioning of sp_configure setting.  Would anyone know the the pros/cons of changing this setting (and best method of doing so).  Am thinking of changing this setting in hopes of overcoming this maintnenance error, thanks in advance.
    Wednesday, March 23, 2011 4:24 PM

Answers

  • Hi,

    I have SQL 2008 (10.0.2531) instance  on my machine. I tried to create a maintenance plan (with one INTEGIRTY CHECK STEP INCLUDING INDEXES) using Local connection & All Databases option enabled. At this time ALLOW UPDATE option under SP_CONFIGURE for this instance was configured to its default values i.e.

    Name           minium       maximum      config_value    run_value

    allow updates 0                  1                   0                    0

    Guess what Maintenance Plan works fine and completed successfully.

    Now I changed ALLOW UPDATE to 1 using

    SP_CONFIGURE 'ALLOW UPDATES',1

    Reconfigure

    Name           minium       maximum      config_value    run_value

    allow updates 0                  1                   1                    0

    I re-execute this maintenance plan and it fails exactly with the same error as you were getting.

    Maintenance Plan Error:

    Error message: Alter failed for Server 'GURPSETH\\SQL2K8'.

    I ran profiler and checked why its failing?

    In Profiler I found errors like below when it was trying to execute SP_CONFIGURE 'USER OPTION',0 ; RECONFIGURE

    Error: 5808, Severity: 16, State: 1

    Ad hoc update to system catalogs is not supported.

    Wait.. why its complaining AD HOC UPDATE TO SYSTEM CATALOGS is not supported? Why its trying to change SYSTEM CATALOGS at first place? In SQL Server 2000 and older, this option allowed users to make updates directly to the system tables. In SQL Server 2005 and newer, the system tables are gone, replaced with the Resource Database and system views. This option is no longer supported in SQL Server 2005 and beyond, and though you can set Allow Updates to 1 with no error, as soon as you run RECONFIGURE, you will receive the error that ad hoc updates are not supported. Because Allow Updates had been set to 1 and my Maintenance Plan runs the RECONGIFURE statement, this error was thrown and the Maintenance Plan failed.

    As per Books On Line:

    Changing the allow updates option will cause the RECONFIGURE statement to fail. Changes to the allow updates option should be removed from all scripts.

    So what I do to resolve is configured ALLOW UPDATE back to 0 and again ran Maintenance Plan and it executes fine.

     


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
    • Proposed as answer by Ali Razeghi Friday, March 25, 2011 7:58 PM
    • Marked as answer by WeiLin Qiao Tuesday, April 5, 2011 2:42 AM
    Friday, March 25, 2011 6:37 AM

All replies

  • It seems that setting Allow Updates = 0 should cure the issue. See this link http://connect.microsoft.com/SQLServer/feedback/details/126163/maintenace-plan-integrity-checks-fail-with-alter-failed-for-server-xxx
    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    Wednesday, March 23, 2011 7:03 PM
  • Thanks for your response Pete, appreciate it.  But are there any negative affects for changing the setting to = 0?  Just trying to avoid breaking/messing things up.
    Wednesday, March 23, 2011 10:23 PM
  • Hi,

    As per SQL 2005 Books On Line:

    The allow updates option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported.

    Now again question comes what exactly is the error that we are getting? Please post it complete.


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
    Thursday, March 24, 2011 4:39 PM
  • Thanks Gursethi also.  The error message is listing it as:

    Server:

    SQLServer2008Machine

     

    Task detail:

    Check Database integrity on Local server connection

    Databases: All databases

    Include indexes

     

    Error number:

    0

     

    Error message:

    Alter failed for Server ' SQLServer2008Machine'.

     

    Thursday, March 24, 2011 4:59 PM
  • Hi,

    I have SQL 2008 (10.0.2531) instance  on my machine. I tried to create a maintenance plan (with one INTEGIRTY CHECK STEP INCLUDING INDEXES) using Local connection & All Databases option enabled. At this time ALLOW UPDATE option under SP_CONFIGURE for this instance was configured to its default values i.e.

    Name           minium       maximum      config_value    run_value

    allow updates 0                  1                   0                    0

    Guess what Maintenance Plan works fine and completed successfully.

    Now I changed ALLOW UPDATE to 1 using

    SP_CONFIGURE 'ALLOW UPDATES',1

    Reconfigure

    Name           minium       maximum      config_value    run_value

    allow updates 0                  1                   1                    0

    I re-execute this maintenance plan and it fails exactly with the same error as you were getting.

    Maintenance Plan Error:

    Error message: Alter failed for Server 'GURPSETH\\SQL2K8'.

    I ran profiler and checked why its failing?

    In Profiler I found errors like below when it was trying to execute SP_CONFIGURE 'USER OPTION',0 ; RECONFIGURE

    Error: 5808, Severity: 16, State: 1

    Ad hoc update to system catalogs is not supported.

    Wait.. why its complaining AD HOC UPDATE TO SYSTEM CATALOGS is not supported? Why its trying to change SYSTEM CATALOGS at first place? In SQL Server 2000 and older, this option allowed users to make updates directly to the system tables. In SQL Server 2005 and newer, the system tables are gone, replaced with the Resource Database and system views. This option is no longer supported in SQL Server 2005 and beyond, and though you can set Allow Updates to 1 with no error, as soon as you run RECONFIGURE, you will receive the error that ad hoc updates are not supported. Because Allow Updates had been set to 1 and my Maintenance Plan runs the RECONGIFURE statement, this error was thrown and the Maintenance Plan failed.

    As per Books On Line:

    Changing the allow updates option will cause the RECONFIGURE statement to fail. Changes to the allow updates option should be removed from all scripts.

    So what I do to resolve is configured ALLOW UPDATE back to 0 and again ran Maintenance Plan and it executes fine.

     


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
    • Proposed as answer by Ali Razeghi Friday, March 25, 2011 7:58 PM
    • Marked as answer by WeiLin Qiao Tuesday, April 5, 2011 2:42 AM
    Friday, March 25, 2011 6:37 AM
  • Thanks everyone, looks like same here also.
    Friday, April 1, 2011 5:42 PM
  • Had the same problem on 2 SQL 2008 R2 servers.
    Thanks for the infos!

    Thursday, July 21, 2011 12:16 PM
  • what's strange is that I'm getting the same error on one of my SQL 2008 R2 servers...

    EXEC sys.sp_configure N'user options', 0 RECONFIGURE

    Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.
    Error: 5808, Severity: 16, State: 1
    Ad hoc update to system catalogs is not supported.

     

    Of course this is the Server Maintenance Utility, not some custom script invoking the configuration change. 

    Running a trace on the 2008 vs. the 2008 R2 server, they look very different... but they both attempt the same sp_configure statements (at different times), but the R2 version throws an exception.

    Tuesday, August 23, 2011 4:44 PM