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.

SCRIPT

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.


Posted

in

by

Comments

One response to “SQL Server maintenance Job Deletion Error”

  1. Ajay avatar
    Ajay

    very useful dear

Leave a Reply to Ajay Cancel reply

Your email address will not be published. Required fields are marked *