How to fix – Error: Alter failed for Server 'Server Name' (Check Integrity task)
Recently I was getting ready for an upcoming demo session and found that my maintenance plan for CHECKDB were failing. When I executed manually, I got below error
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.
I followed error message and checked the history of maintenance plan, here was the error.
Failed: (0) Alter failed for server ‘ComputerName\InstanceName’
My research yielded https://support.microsoft.com/en-us/kb/945067 which was talking about incorrect setting of Allow Updates in sp_configure. In my case it was just fine. I have put profiler and found below statement failing.
EXEC sys.sp_configure N'user options', 0 RECONFIGURE
When I executed below statement from SQL Server Management Studio, I got below error
Configuration option ‘user options’ changed from 0 to 0. Run the RECONFIGURE statement to install.
it was recovery interval value which I as 10000, was the problem. Once I bought it down to 60, I was able to run the maintenance plan without any problem.
sp_configure 'recovery interval (min)', 60
GO
RECONFIGURE WITH override
GO
As shown above, the maintenance plan is using the "reconfigure" command and getting an error. Due to that it wasn’t able to proceed further and failing with "Alter failed for server" error. Hope it’s clear that if you are getting the same error, you should run the RECONFIGURE command from SQL Server Management studio and check the any error. Here are the various errors which I saw.
Later, when I tried to reproduce the same on SQL 2012 and SQL Server 2014, I was not able to. This is a good news that the issue is fixed by Microsoft in later versions.
Have you ever found the same error and the solution was other than three which I mentioned? Please comment and let me know so that it can help others also.
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.
I followed error message and checked the history of maintenance plan, here was the error.
Failed: (0) Alter failed for server ‘ComputerName\InstanceName’
My research yielded https://support.microsoft.com/en-us/kb/945067 which was talking about incorrect setting of Allow Updates in sp_configure. In my case it was just fine. I have put profiler and found below statement failing.
EXEC sys.sp_configure N'user options', 0 RECONFIGURE
When I executed below statement from SQL Server Management Studio, I got below error
Configuration option ‘user options’ changed from 0 to 0. Run the RECONFIGURE statement to install.
it was recovery interval value which I as 10000, was the problem. Once I bought it down to 60, I was able to run the maintenance plan without any problem.
sp_configure 'recovery interval (min)', 60
GO
RECONFIGURE WITH override
GO
As shown above, the maintenance plan is using the "reconfigure" command and getting an error. Due to that it wasn’t able to proceed further and failing with "Alter failed for server" error. Hope it’s clear that if you are getting the same error, you should run the RECONFIGURE command from SQL Server Management studio and check the any error. Here are the various errors which I saw.
- Make sure that "Allow updates" is 0
- Make sure that the "recovery interval (min0" is less than 60
- Make sure that the service account is having "Lock Pages in Memory" when AWE is enabled.
Later, when I tried to reproduce the same on SQL 2012 and SQL Server 2014, I was not able to. This is a good news that the issue is fixed by Microsoft in later versions.
Have you ever found the same error and the solution was other than three which I mentioned? Please comment and let me know so that it can help others also.
No comments:
Post a Comment