I recently ran into an odd little problem with a Maintenance Plan that I wanted to share. I had a plan that was running the Check Database Integrity Task that suddenly started failing. Nothing about the databases had changed and if you ran a manually DBCC CHECKDB all the databases came back clean. The job history showed a meaningless, truncated message:
Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:14:30 AM Finished: 10:14:31 AM Elapsed: 1.185 seconds. The package execution failed. The step failed.
When looking at the Maintenance Plan history directly, I had an error that stated: Alter failed for Server 'ejohnsonmobile'. Really nothing at this point has provided any details into a possible fix. Next step was to run a Profiler trace to see what was really going on. When setting up a trace to look for errors, be sure to include the User Error Message and Exception events. Since this is a Maintenance Plan, which is just an SSIS Package, the trace will show you that there is a lot more going on than just the CHECKDB. When looking for errors in the trace I noticed an exception just after the following query was run:
EXEC sys.sp_configure N'user options', 0 RECONFIGURE
The exception this caused was:
Error: 5808, Severity: 16, State: 1
Ad hoc update to system catalogs is not supported.
That’s a little odd, the query was modifying the User Options configuration value, why would we see an error about Ad Hocs updates? Here is where the problem becomes clear. At some point, the Allow Updates configuration option was set to 1. In SQL Server 2000 and older, this option allowed users to make updates directly to the system tables. In SQL Server 2005 and newer, the system tables are gone, replaced with the Resource Database and system views. This option is no longer supported in SQL Server 2005 and beyond, and though you can set Allow Updates to 1 with no error, as soon as you run RECONFIGURE, you will receive the error that ad hoc updates are not supported. Because Allow Updates had been set to 1 and my Maintenance Plan runs the RECONGIFURE statement, this error was thrown and the Maintenance Plan failed.
All I had to do was run “sp_configure 'Allow Updates', 0” to set Allow Updates back to 0 and then everything started working again.