Delete statement conflicts with reference constraint fk_subplan_job_id The job cannot delete the workaround

After you delete a running maintenance plan on SQL Server 2008, the maintenance plan job is not automatically deleted, and the maintenance plan job is manually removed, prompting for the deletion to fail.

Today also encountered this problem, the Internet to find the next, copy over! Beware of the next problem to find some!

Error Symptom:Delete statement conflicts with reference constraint "fk_subplan_job_id". The conflict occurred in the database "msdb", table "Dbo.sysmaintplan_subplans", and column ' job_id '. Statement has been terminated. (Microsoft SQL Server, error: 547)

There are three tables related to the maintenance plan:
1.sysmaintplan_log (Record maintenance plan run log);
2.sysjobschedules (Record the job information of agent job);
3.sysmaintplan_subplans (Record the sub-plan information of the maintenance plan);
4.sysjobs_view (related job information)

The above 4 tables have the relationship between PK and FK, when deleting the job, it is easy because of the FK conflict caused by the failure.

The specific processing methods are as follows:

Use [msdb]declare @job_name varchar (+) Set @job_name = N ' official library daily 3:00 second full backup. Subplan_1 '--note: job_name the job name--for the maintenance plan delete the log in the plan delete sysmaintplan_log from Sysmaintplan_subplans as Subplansinner join Sysjobs_view as syjobs on subplans.job_id = Syjobs.job_idinner join Sysmaintplan_log on Subplans.subpla n_id =sysmaintplan_log.subplan_idwhere (syjobs.name = @job_name)--delete agent jobs delete sysjobschedules from Sysjobs_view Vinner join Sysjobschedules o on v.job_id=o.job_id where [email protected]_name--Delete sub-plan delete Sysmaintplan_subplans from S Ysmaintplan_subplans as Subplansinner join Sysjobs_view as syjobs on subplans.job_id = syjobs.job_idwhere (Syjobs.name = @ job_name)--delete the job delete from Msdb.dbo.sysjobs_view where name = @job_name--Query which jobs are available select * from Msdb.dbo.sysjobs_view

　　

Portal: http://kms.lenovots.com/kb/article.php?id=14108

The

Delete statement conflicts with the reference constraint fk_subplan_job_id, causing the job to not remove the workaround