Troubleshooting a Failed Maintenance Plan
Pair up a Profiler trace with the User Error Message and Exception events, and problems will reveal themselves quickly.
- By Eric Johnson
I recently ran into an odd little problem with a Maintenance Plan that I want to share. I had a plan that runs the Check Database Integrity Task that suddenly started failing. Nothing about the databases had changed and when I manually ran a 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 noticed an error that stated:
Alter failed for Server 'ejohnsonmobile'
Really nothing at this point provided any details into a possible fix.
The 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, so 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 although 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.
Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology
consulting. He is also the President of the Colorado Springs SQL Server
User Group. He can be contacted at www.consortioservices.com.