Troubleshooting a Failed Maintenance Plan -- Redmondmag.com

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
12/21/2009

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

To Support AI Workloads, Microsoft Taps Lumen for Network Boost

With AI workloads demanding more from its datacenters, Microsoft is looking to outside partners for infrastructure support.
Which Enterprises Should Ditch Passwords? All of Them!

As security biometrics continue to sophisticate, many organizations are still using flawed passwords to safeguard their data. That needs to change. Now.
CrowdStrike Blames Internal Testing Faults for Mass Outage

In an initial post-mortem, Austin-based security firm CrowdStrike said that an issue in its testing software led to the company pushing through a faulty update that took down more than 8.5 million Windows systems last week.
Build Your Own Custom Copilot, Part 2: Making the Connection

Now that you have a plan, it's time to create your personalized Copilot.
Microsoft Releases CrowdStrike Outage Recovery Tool

Microsoft has released a tool to help recover affected systems after last week's global outage caused by a faulty update pushed through by security firm CrowdStrike.