Troubleshooting a Failed Maintenance Plan

Pair up a Profiler trace with the User Error Message and Exception events, and problems will reveal themselves quickly.

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.

About the Author

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.

Featured

  • Microsoft Drops 'Solorigate' for 'Nobelium' in Ongoing SolarWinds Attack Investigations

    Microsoft this week described "three new pieces" of malware that were used in the SolarWinds Orion espionage attacks dubbed "Solorigate," although Microsoft security researches are now calling it "Nobelium."

  • Microsoft Universal Print Service Commercially Released

    Microsoft announced on Tuesday that its Universal Print service is now commercially released at the "general availability" stage worldwide.

  • Restoring a Backup to Dissimilar Hardware: 3 Things To Watch Out For

    Getting a new desktop looking and feeling like the old one used to take a long time, but modern backup applications have greatly streamlined the process. Still, there are a few things to keep in mind to avoid potential issues.

  • Black Box

    Microsoft Releases Windows Server 2022 Preview

    Microsoft announced during its Ignite event that Window Server 2022 is currently availability at the preview stage.

comments powered by Disqus