delete sql maintenance plan

[office politics]

2005 by default was throwing errors relating to foriegn key constraints. i ran the following code and was able to delete the maintenance plan.

Sql Server 2005 Delete Maintenance Plan Error Gediminas (Gedas) Gudenas

Sql Server 2005 Delete Maintenance Plan Error
April 20, 2007 · 26 Comments
Below is the manual SQL fix to address this error:

Drop failed for Job ‘XXXXXXXXXXXXX'. (Microsoft.SqlServer.Smo)

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. (Microsoft SQL Server, Error: 547)

Here is the SQL to fix this:

–NOTE: Replace “MaintenancePlan” with the value in the Jobs under SQL Server Agent, put the job name

USE [msdb]
declare @job_name varchar(100)
set @job_name = N'MaintenancePlan'


–First, 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 actual job (You can do the same thing through Management Studio (Enterprise Manager)

delete
from msdb.dbo.sysjobs_view where name = @job_name


If you get this error:

Msg 547, Level 16, State 0, Line 27
The DELETE statement conflicted with the REFERENCE constraint “FK__sysjobsch__job_i__276EDEB3″. The conflict occurred in database “msdb”, table “dbo.sysjobschedules”, column ‘job_id'.

Open the Job and Delete the Schedules