On Premise and Cloud Database Knowledge Base

SQL Server maintenance Job Deletion Error

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

1 comment

Leave a Reply

Your email address will not be published.