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)
The following query should help you to identify the associated maintenance plan.
select p.name as 'MaintenancePlan',sp.subplan_name as 'SubplanName',j.name as 'Job Name',j.[description] as 'Job Description' from msdb..sysmaintplan_plans p inner join msdb..sysmaintplan_subplans sp on p.id = sp.plan_id inner join msdb..sysjobs j on sp.job_id = j.job_id
To Resolve I did the following:
USE [MSDB] GO -- View the Maintenance plan subplans select * from sysmaintplan_subplans -- View the Maintenance plan logs select * from sysmaintplan_log
To Delete the subplan:
USE [MSDB] go --Delete the Log history for the maintenance plan affected DELETE FROM sysmaintplan_log WHERE subplan_id in ( SELECT Subplan_ID from sysmaintplan_subplans -- change Subplan name where neccessary WHERE subplan_name = 'Subplan_1' ) -- Delete the subplan DELETE FROM sysmaintplan_subplans WHERE subplan_name = 'Subplan_1'
You have maintenance plans. Try to follow these steps:
Find the maintenance plan name and id that you want to delete. Write down the id of the one you want to delete.
SELECT name, id FROM msdb.dbo.sysmaintplan_plans
--Place the id of the maintenance plan you want to delete into the below query to delete the entry from the log table:
DELETE FROM msdb.dbo.sysmaintplan_log WHERE plan_id = ''
Place the id of the maintenance plan you want to delete into the below query and delete the entry from subplans table:
DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE plan_id = ''
Place the id of the maintenance plan you want to delete into the below query to delete the entry from the plans table:
DELETE FROM msdb.dbo.sysmaintplan_plans WHERE id = ''
Now you can delete the jobs from Management Studio.
******************************************************************************************************************************************
--NOTE: Replace “MaintenancePlan”
USE [msdb]
declare @job_name varchar(100) set @job_name = N’MaintenancePlan’ --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 delete from msdb.dbo.sysjobs_view where name = @job_name