Maintenance Plan Issue: Alter Failed for Server ”

Problem:

Maintenance plan fails when it executes the database integrity check task with the following error: “Alter failed for Server ‘<servername>’.”  This issue is related to the “Allow Updates” advanced option for SQL server.  When this option is set to 1, an exeption is thrown any time the “REGONFIGURE” statement is executed.  During the execution of the maintenance plan, it executes the “RECONFIGURE” command which causes to raise an error.

Solution:

The “Allow Updates” option has to be set to 0.

The following two steps need to be executed on the server after verifying that the advance option is set to 1:

USE master;

GO

–Step 1

–SET option to false

EXEC sp_configure ‘ALLOW UPDATES’,‘0’;

GO

RECONFIGURE

GO

–Step 2

–Verify the option has been reconfigure to 0

EXEC sp_configure

GO

–Additional Info:

— This setting will display all configuration options

EXEC sp_configure ‘show advanced option’,‘1’;

GO

RECONFIGURE

GO

EXEC sp_configure

GO

— This setting will display a limited list of configuration options

EXEC sp_configure ‘show advanced option’,‘0’;

GO

RECONFIGURE

GO

References:

http://connect.microsoft.com/SQLServer/feedback/details/126163/maintenace-plan-integrity-checks-fail-with-alter-failed-for-server-xxx

http://sqlblog.com/blogs/eric_johnson/archive/2009/12/23/troubleshooting-a-failed-maintenance-plan.aspx

Advertisements