When attempting to delete a job in a SQL Server maintenance plan, you may encounter an error message similar to the following:

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)

This error message indicates that there is a conflict with a reference constraint when attempting to delete the job. Specifically, the “job_id” column in the “dbo.sysmaintplan_subplans” table conflicts with the “id” column in the “sysmaintplan_plans” table. In order to resolve this issue, you need to delete the maintenance plan associated with the job first.

To resolve this error, you can follow these steps:

1. Delete all log data associated with the job in the “sysmaintplan_log” table.
2. Delete all subplan data associated with the job in the “sysmaintplan_subplans” table.
3. Delete the maintenance plan associated with the job in the “sysmaintplan_plans” table.
4. Finally, delete the job.

Here’s a detailed explanation:

1. First, select the list of jobs to be deleted and click on ‘Script Job as’ -> ‘DROP’ -> ‘New Query Editor Window’.

2. The query execution window appears and the job_id is confirmed. Copy this job_id.

3. Create a new query window and enter the following command as shown below:

use master
select * from msdb.dbo.sysmaintplan_subplans where job_id='19c04eaf-2ec4-47fe-816c-0ba0d23874d88'

When you enter the command, the plan_id value is output. Now copy this value.

4. After copying the plan_id value above, delete it using the delete query statement below:

use master
delete from msdb.dbo.sysmaintplan_log where plan_id='8759DB1-A425-47F8-94C3-0B0DCF177277'

delete from msdb.dbo.sysmaintplan_subplans where plan_id='8759DB1-A425-47F8-94C3-0B0DCF177277'

5. Now try deleting the part that caused the error. You can confirm that the deletion was successful.

All deletions have been completed successfully.