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 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.
Msg 5807, Level 16, State 1, Line 1Recovery intervals above 60 minutes not recommended.
Use the RECONFIGURE WITH OVERRIDE statement to force this configuration.
In my case, 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.
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
what does it mean server error while i am downloading a report. the software leaves a server error message,like- “the maximum report processing jobs limit configured by your system administrator has been reached” what does it means . please explain it in very simple words, if possible.
+Adding to it
I found the same issue and all the 3 below options were already present.
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.
On further checking found that the option ‘Automatically set I/O affinity mask for all processors’ was unchecked in server properties so I have enabled it and re run the job which executed successfully :)
Thanks @Mayur – I guess any setting which can cause reconfigure to fail would cause this error.
Had the same issue on a SQL 2012 (11.0.5343) server. Changed the Allow Updates to 0(others were already set) and then it started working. Never had this happen on other servers, and we have many.