Changes in Management Plan overwrites owner of SQL Job

Problem: 

In one of the production SQL Servers, I created a new database.  After which I added that database into the existing Maintenance Plan, meant for Daily backup (This maintenance plan was created long back by another DBA, with his account).  Next day, I noticed that the backup job had failed.  While investigating the issue, I found that the job owner was changed from sa account to old DBA account (Old DBA moved out of organisation long back).

 

Reason:

When you change anything on a maintenance plan created by another person, the original creator will be stored as the owner of the job every time you make a change in the SSIS package. This happens because SQL will try to keep the owner of both objects in sync. But the owner of the package is not exposed, so you have to update MSDB by hand to change it to any other thing – lets say “sa” for example.

 

Solution:

Code to update the package owner.  Please refer the below link as well.

https://connect.microsoft.com/SQLServer/feedback/details/295846/job-owner-reverts-to-previous-owner-when-scheduled-maintenance-plan-is-edited

 

--view all packages
select * from msdb.dbo.sysssispackages
--update plans ownership
update msdb.dbo.sysssispackages
set [ownersid] = suser_sid('sa')
where [name] like 'MaintenancePlan%'
Note: Replace the name and owner account in the script as per your requirement.
Advertisements

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