Google Custom Search

Monday, May 5, 2008

SQL Server 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.