Search This Blog

SQL Server 2008: Maintenance Plan error - 'Alter failed for Server...'

We had setup a maintenance plan for doing the weekly integrity check which was working fine for the past few weeks and all of the sudden, last week it failed;I spent few hours on internet figuring out the solution for this; As on every other forums, i thought the issue could be related to ‘sp_Configure’. I checked the value of ‘Allow updates’ , it was 0, but still want to try the solution most of the people recommended.



This didn’t fix my issue, I still got the same error; So I choose to run Profiler to see where it is failing; In order to see the errors, I enabled ‘Background job error’ and ‘user error messages ‘on “Errors and Warnings “ Event section; this again showed the error occurred after the maintenance plan ran “Allow Updates”; I did invest more time on this, turning on and off different user configurable options using ‘sp_configure’ and on one such occasion, I ran ‘RECONFIGURE’ instead of ‘RECONFIGURE WITH OVERRIDE’ which show me a warning

Msg 5807, Level 16, State 1, Line 1

Recovery intervals above 60 minutes not recommended. Use the RECONFIGURE WITH OVERRIDE statement to force this configuration

Immediately I realised that this could be the root cause; I ran sp_Configure and saw, someone set the value for “Recovery Interval (min)’ to 120 which was not recommended. And since maintenance plan was not using the reconfigure with ‘WITH OVERRIDE’ option, it was getting this error and because of this, it wasn’t able to proceed further, and when i set that back to zero the job started running again.

EXEC sp_configure 'recovery interval (min)', 0


Just to keep it short, here are my findings of my research from the internet for maintenance plan to fail

  1. Make sure that ‘Allow updates’ is 0 , use the first script to do this
  2. Make sure that the Min recovery interval is < 60 ,
  3. Another solution, if you have enabled AWE, disable it , and change the users group policy to grant “Lock Pages in Memory”

If you find more options, please comment, i will update the list.


1 comment:

Mayur Jain said...

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 :)