SQL Server- Issue & Solution

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