Troubleshooting a Failed Maintenance Plan DBCC

Problem:

I recently ran into an odd little problem with a Maintenance Plan that I would like 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 when i ran DBCC CHECKDB manually it completed successfully. The job history showed a meaningless, truncated message:


 

Job History:

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: Elapsed: 1.185 seconds. The package execution failed. The step failed.

Maintenance Plan Error History:

Alter failed for Server “ServerNAme”


 

 

Error

 

Solution: 

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 the below command to set Allow Updates back to 0 and then everything started working again.

 

Command:

sp_configure  ‘Allow Updates’, 0

Reconfigure with override

 

ErrorResolved

 

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: