When you encounter a job that is generated by the agent before you delete the running Maintenance plan on SQL Server 2005, you are prompted to delete the failure. The delete statement conflicts with the 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: After the maintenance plan is run, it is recorded in this table; 2.sysjobschedules: Job information for agent jobs 3.sysmaintplan_subplans: Records the sub-plan information for the maintenance plan 4.sysjobs_view: Related job information These 4 tables have an association between PK and FK, and when the job is deleted, It is easy to fail due to the collision of FK. Specific processing methods are as follows: Use [msdb]declare @job_name varchar (Set @job_name = N ' Hostpitaltestplan.subplan_reset ' -- Delete Log in plan delete Sysmaintplan_logfrom Sysmaintplan_subplans as Subplans INNER Joinsysjobs_view as Syjobs on Subplans.job _id = syjobs.job_id INNER joinsysmaintplan_log on subplans.subplan_id = sysmaintplan_log.subplan_idwhere (Syjobs.name = @ job_name) --Delete Agent job Delete sysjobschedulesfrom Sysjobs_view v INNER JOIN sysjobschedules o on v.job_id=o.job_id WHERE [email protected]_name --Delete the sub-plan delete Sysmaintplan_subplansfrom Sysmaintplan_subplans as Subplans INNER Joinsysjobs_view As syjobs on subplans.job_id = syjobs.job_idwhere (syjobs.name = @job_name) --Delete job Delete from Msdb.dbo.sysjobs_view WHERE NAME = @job_name
SQL Server Agent job deletion failed problem