I have found in discussion forums regarding the error given below when you try to delete a job that was part of maintenance.
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. (.Net SqlClient Data Provider)
This error is caused not in all systems in some and it depends. I have tried to replicate the error but it would be always unsuccessful. Anyways the solution to can be handled with the script given below.
DECLARE @jobname VARCHAR(100) SELECT @jobname='PASS YOUR NAME OF THE JOB' DELETE FROM msdb.dbo.sysmaintplan_log WHERE plan_id IN( SELECT plan_id FROM msdb..sysmaintplan_subplans WHERE job_id IN( SELECT job_id FROM sysjobs WHERE name =@jobname)) DELETE FROM sysmaintplan_subplans WHERE job_id IN( SELECT job_id FROM sysjobs WHERE name =@jobname)
Then you can delete your job. Hope this post helps you.
Leave a Reply