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.
EXEC SP_CONFIGURE 'ALLOW UPDATES',0
RECONFIGURE WITH OVERRIDE
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
RECONFIGURE WITH OVERRIDE
Just to keep it short, here are my findings of my research from the internet for maintenance plan to fail
- Make sure that ‘Allow updates’ is 0 , use the first script to do this
- Make sure that the Min recovery interval is < 60 ,
- 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.
