Search This Blog

Sunday, March 27, 2011

SQL 2008 Maintenance Plan Error - 'Alter failed for Server '

Hi,

One of my fellow DBA team member came to me with a problem, but let me provide descriptions of this first.

Description
************


we have a recetnly installed server ofcourse SQL Server 2008, created a maintenance plan with CHECK DATABASE INTEGRITY TASK with INCLUDE INDEXES option selected.



When we execute this maintenance plan it fails with below error:




So as the screenshot mentions that plan is failing because its not able to perform ALTER task in current SQL Instance.

Now why is it so? I am just created a Maintenance Plan with Database Integrity task and its failing? Why?

Troubleshooting
***************


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 i checked current setting SP_CONFIGURE and found ALLOW_UPDATE config value as 1. So i configured ALLOW UPDATE back to 0 and again ran Maintenance Plan and it executes fine.

Below is the command used for setting this option

SP_CONFIGURE 'ALLOW UPDATE',0
RECONFIGURE


Regards
Gurpreet Sethi

10 comments:

Anonymous said...

Thank you! Worked like a charm for me!!!

Anonymous said...

Thank you! Worked like a charm for me!!!

Anonymous said...

http://sequelserver.blogspot.ca/2013/04/sql-server-2008-maintenance-plan-error.html

Anonymous said...

Thanks, this worked perfectly.

Never had this issue on any of our other SQL servers before, bizarre!

Tharaka said...

Thanks. It worked.

elson cade said...
This comment has been removed by the author.
elson cade said...
This comment has been removed by the author.
elson cade said...

Server buddies provides Quality remote server management including troubleshooting, Server Management, Plesk Support, Server Maintenance, Server Monitoring, Server Troubleshooting and support for a large variety of customers at affordable rates. Here are option for Server Management, Server Maintenance, Server Monitoring, Server Troubleshooting, Server Optimization, Plesk Support, Linux Support, cPanel Support and Plesk Support.
.........

Michael said...

I also had the same problem and your approach did indeed solve it. Thanks a lot!

latesttechnologyblogs said...

Really good information to show through this blog. I really appreciate you for all the valuable information that you are providing us through your blog. SQL server dba Online Course