THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

Troubleshooting a Failed Maintenance Plan

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.

Published Wednesday, December 23, 2009 2:20 PM by ejohnson2010



Ken Powers said:

Thanks for posting this.  I had the exact same problem with one of my maintenance plans.

January 12, 2011 1:27 PM

David said:

Thanks for this. Exact same problem here too.

February 7, 2011 5:43 AM

PC said:

Thank you for posting this!  You saved me.  I had looked up different sites but you got it right on.

June 7, 2011 11:25 AM

Cris said:

Thank you. I also had this problem, I did this to myself. I had forgotten about changing "Allow Updates" to 1 and then my plan failed miserably.

October 6, 2011 11:19 AM

Guillermo said:

Many thanks for your post. You saved me.

October 26, 2011 9:57 AM

DatWunGai said:

Thank you so much for demystifying what is just one of a long, frustrating line of uninformative, and otherwise useless error messages from microsoft!

January 25, 2012 1:31 PM

Alex said:

Thanks! That was exactly my case

April 6, 2012 2:50 AM

Vinit Satam said:

Thank you buddy.......

May 2, 2012 3:59 PM

JP said:

Thanks very much for posting. Wish I had found this.

May 10, 2012 2:31 PM

Venu said:

Wow... this solution worked like a charm for me..

the error I got is exactly the same and couldn't useful info on other sites. I re-created the subplan, I recreated the whole maintenance plan still I was facing issue at check integrity task. rebuild index was running fine.

Thanks again!

October 15, 2012 5:38 PM

Flavio said:

Thank You for the posting.

And for figuring how to decipher another meaningless error message.

I appreciate it !

November 28, 2012 10:47 AM

Aneesh said:

If you set a value greater than 60 for 'recovery interval', it throws the same error

April 4, 2013 12:48 PM

saranya said:

Maintenance plan tab is not listed in sql server2008. Any solutions for this.

May 6, 2013 8:59 AM

Raj Thiagarajan said:

Wow.  Thanks a lot for this posting.

August 27, 2013 7:51 PM

Dave Bower said:

Awesome, was the exact problem. Not sure what changed this setting but your fix worked !

Thank you

January 13, 2014 1:13 PM

Hammer said:

Appreciate the post, this solution fixed the problem.

April 15, 2014 11:30 AM

Joe said:

Thank you.

May 5, 2014 1:48 PM

Matimba said:

Hi, Thanks a lot this helped solve my checkdb problems

May 19, 2014 5:38 AM

sudarshan said:

Thank you so much

July 25, 2014 10:04 AM

Natalie said:

THANK YOU THANK YOU for taking the time to write this!  I have no idea how that switch got changed - you saved me a ton of time

August 8, 2014 10:06 AM

Mohit Sengar said:

Thank you so much Eric...

September 16, 2014 3:43 AM

Judith Enever said:

Thank you.  Somehow the switch was changed on a server and stopped my backup procedure.  Looks like the problem is now resolved!

January 7, 2015 11:08 PM

Bram said:

Thank you!

February 13, 2015 9:23 AM

Gordon said:

Same problem. Thanks for saving me a lot of trouble.

April 29, 2015 3:46 AM

Mintaka said:

Thanks, I do confirm that this solves the issue.

In the log of the task, there was the error:

Failed:(0) Alter failed for Server 'DBSERVER\\DBNAME'.

In the ERRORLOG file there was:

2015-12-29 22:00:02.87 spid57      Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.

December 30, 2015 9:35 AM

Allan B said:

Thanks Eric - you've just saved me hours of head scratching!

Others out there having this issue, remember to filter on that error code in profiler - if it recorded it, check for that flag, reconfigure - and if you still have this issue, move on!

May 9, 2016 8:05 AM

Renny Park said:

Thanks Eric.

It's really helpful posting.

March 2, 2017 9:05 PM
New Comments to this post are disabled

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Privacy Statement