10.12.07

Job from removed maintenance plan

Posted in DBA, Microsoft Sql server, TSQL at 11:36 am by Hedwig Lodrigo

You removed a maintenance plan and it didn’t delete the job. You got the following error:

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)

You can remove the job by executing the following code:

– Set @job_name to the job name you want to delete

USE [msdb]
DECLARE @job_name varchar(100)
SET @job_name = ‘job name’

–Delete the logs for the plan

DELETE
sysmaintplan_log
FROM
sysmaintplan_subplans AS subplans
INNER JOIN sysjobs_view AS syjobs ON
subplans.job_id = syjobs.job_id
INNER JOIN sysmaintplan_log ON
subplans.subplan_id = sysmaintplan_log.subplan_id
WHERE
syjobs.name = @job_name;

–Delete the subplan

DELETE
sysmaintplan_subplans
FROM
sysmaintplan_subplans AS subplans
INNER JOIN sysjobs_view AS syjobs ON
subplans.job_id = syjobs.job_id
WHERE
syjobs.name = @job_name;

–delete the job

DELETE FROM
msdb.dbo.sysjobs_view
WHERE
name = @job_name;

Comments are closed.