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.
Answer
---------------
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 ' Server Name'.
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.
Answer
---------------
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 ' Server Name'.
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.
No comments:
Post a Comment