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

 

Linked Server Error–remote proc transaction promotion

Problem:  

I have two SQL Servers Server A and Server B. Both have SQL Server 2008. I needed to fetch data from server B to server A and insert it into a table on server A.  For this purpose, I had created a stored proc on server B and for calling that SP I have added server B as linked server on server A.  When I executed this SP from server A using server B, it returns data without any issue.

However when I try to insert this data to a local table on server A, I am getting following error:

OLE DB provider “SQLNCLI10” for linked server “LinkedServerName” returned message “The transaction manager has disabled its support for remote/network transactions.”.
Msg 7391, Level 16, State 2, Server <ServerName, Procedure <SP Name>, Line 47
The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “LinkedServerName” was unable to begin a distributed transaction.

 

Solution:

Resolved the issue by changing the linked server property with below command.

EXEC sp_serveroption @server = ‘ServerB’,@optname = ‘remote proc transaction promotion’, @optvalue = ‘false’ ;