Msg 547 Level 16 State 0 sp_delete_job

SQL Server : Deleting job for a deleted maintenance plan.

On certain occasions I have seen that deleting a maintenance plan is not sufficient and it leaves behind the job that was created as part of the maintenance plan. If you tried to delete the job after deleting the maintenance plan then you will get the following message.





Msg 547, Level 16, State 0, Procedure sp_delete_job, Line 178
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.

Looks like there are some remnants of the maintenance plan that did not get deleted from some system views in the MSDB database.

To fix this take the following steps.

1) Get the name of the job
2) Run the following command

        Use msdb

        delete 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)

3) Then run the following command

        Use msdb

        delete FROM sysmaintplan_subplans AS subplans INNER JOIN
        sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id
        WHERE (syjobs.name = @job_name)

Make sure you replace @job_name with the actual job name.