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

  • Google IDs on Azure Active Directory B2B Service Now at 'General Availability'

    Microsoft announced on Wednesday that users of the Google identity and access service can use their personal log-in IDs with the Azure Active Directory B2B service to access resources as "guests."

  • Top 4 Overlooked Features of a Data Backup Strategy

    When it comes to implementing an airtight backup-and-recovery plan, these are the four must-have features that many enterprises nevertheless tend to forget.

  • Microsoft Bolsters Kubernetes with Azure Confidential Computing

    Microsoft on Tuesday announced various developments concerning the use of Kubernetes, an open source container orchestration solution fostered by Google.

  • Windows Will Have Support for Encrypted DNS

    Microsoft announced this week that the Windows operating system already has support for an encrypted Domain Name System option that promises to add greater privacy protections for Internet connections.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.