SQL Server : Msg 547, Level 16, State 0, Procedure 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)  In the following commands replace 'yourjobname' with your job name and run the following command

       Use msdb

       delete sysmaintplan_log
       FROM sysmaintplan_subplans AS subplans INNER JOIN
       sysjobs_view AS sysjobs ON subplans.job_id = sysjobs.job_id INNER JOIN
       sysmaintplan_log ON subplans.subplan_id = sysmaintplan_log.subplan_id
       WHERE (sysjobs.name = 'yourjobname')

        Use msdb

        delete sysmaintplan_subplans
        FROM sysmaintplan_subplans AS subplans INNER JOIN
        sysjobs_view AS sysjobs ON subplans.job_id = sysjobs.job_id
       WHERE (sysjobs.name = 'yourjobname')