Below is the manual SQL fix to address this error:
Drop failed for Job ‘XXXXXXXXXXXXX’. (Microsoft.SqlServer.Smo)
The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’.
The statement has been terminated. (Microsoft SQL Server, Error: 547)
Here is the SQL SCRIPT to fix this:
NOTE: Replace “MaintenancePlan” with the value in the Jobs under SQL Server Agent, put the job name
=========
USE [msdb]
declare @job_name varchar(100)
set @job_name = 'CRS Daily Maintenance.Subplan_1'
-- First, delete the logs for the plan
delete sysmaintplan_log
FROM sysmaintplan_subplans AS subplans INNER JOIN
sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id INNER JOIN
sysmaintplan_log ON subplans.subplan_id = sysmaintplan_log.subplan_id
WHERE (syjobs.name = @job_name)
-- Delete the Subplan
delete sysmaintplan_subplans
FROM sysmaintplan_subplans AS subplans INNER JOIN
sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id
WHERE (syjobs.name = @job_name)
-- Delete the actual job (You can do the same thing through Management Studio (Enterprise Manager)
delete
from msdb.dbo.sysjobs_view where name = @job_name
==========
Above Script will fetch Job ID for maintenance plan and Sub Maintenance Plan from the system.
==========
If you get this error:
The DELETE statement conflicted with the REFERENCE constraint "FK__sysjobsch__job_i__276EDEB3".
The conflict occurred in database "msdb", table "dbo.sysjobschedules", column 'job_id'.
Open the Maintenance Sub Job Plan Under SQL JObs Agent and Delete the Schedules and Run the Above Query Again – It will Delete the Maintenance & Sub Maintenance Job
- You can create new Maintenance Plan As you require.
From
http://www.developmentnow.com/g/118_2006_10_0_0_831628/Cannot-Delete-Maintenance-Plans--SQL-2005.htm Posted via DevelopmentNow.com Groups
