Gediminas (Gedas) Gudenas

←

Sql Server 2005 Delete Maintenance Plan Error

April 20, 2007 · 29 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

Possibly related posts: (automatically generated)


Categories: Tech

29 responses so far ↓

  • Peter Gadsby // April 25, 2007 at 10:49 am | Reply

    Thanks, this was really useful, do you know why this issue occurs? Thanks Peter Gadsby

  • gedzuks // April 25, 2007 at 5:29 pm | Reply

    Let’s say you run a maintenance plan wizard.
    When the maintenance plan is created SQL Server will create a Job, Schedule and an SSIS package as a reference to SSIs package called sub. In short it makes an inserts into these msdb database tables:

    sysjobs_view
    sysmaintplan_subplans
    sysjobschedules

    When you execute a job it makes more instert into log table: sysmaintplan_log

    All of these tables are linked through FK and PK relationships. The problem is when you try to delete a job there are no triggers or referential integrity for delete action to cascade the deletes. So it gives a Foreign Key errors until you manually remove those entries by the SQL above.

    Microsoft probably did not want to delete subplans, because they might be referenced by many jobs. They probably should have wrote a better delete script that asks if user wants to delete logs, schedules, subplans etc. to take care of deletes. Instead, they just try to delete an entry in the sysjobs_view tables.

  • Marina Plesovska // April 26, 2007 at 5:05 pm | Reply

    This worked great! Thanks for your help!

  • Rob Whittaker // June 26, 2007 at 8:18 am | Reply

    Excellent! Solved my problem. Thanks.

  • Edgardo Accorinti // July 3, 2007 at 1:21 pm | Reply

    Thanks from Argentina!
    Solved my problem.
    Now, i can’t delete the maintenance plan :(

  • Loknar // August 28, 2007 at 6:21 am | Reply

    check and delete from this msdb table:

    delete
    –select *
    FROM sysmaintplan_plans

  • Julien // September 27, 2007 at 9:52 am | Reply

    Thanks a lot for your solution. This help me !

  • Fredster // October 4, 2007 at 1:04 am | Reply

    Awesome – thank you so much!

  • Don Rodger // October 24, 2007 at 5:34 pm | Reply

    This just saved hours of frustration. Thanks a lot.

  • John Ko // November 13, 2007 at 5:47 am | Reply

    This solution help to solve my problem, thank you very much!!!

  • Imran // January 24, 2008 at 12:47 am | Reply

    Excellent,

    Saved me from calling Microsoft.

    Thanks,

  • Антон // February 19, 2008 at 6:40 am | Reply

    Спасибо очень помогло

  • Murtuja Khokhar // February 27, 2008 at 12:31 pm | Reply

    Hi,

    Thanks for this solution.I am posting complete steps.

    USE [msdb]
    declare @job_name varchar(100)
    set @job_name = N’TransactionLogbackup.Subplan_1′

    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 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 from dbo.sysjobschedules where job_id in (select job_id from msdb.dbo.sysjobs_view where name = @job_name)

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

    delete from sysmaintplan_plans where NAME=’Maintenance Plan Name’

  • Ritchie // April 2, 2008 at 2:19 pm | Reply

    Oh Lord! Thank you so much! It works fine and was very helpful!

  • Patrick // May 14, 2008 at 7:50 am | Reply

    Thanks that was helpful

  • Samuel // May 20, 2008 at 2:04 pm | Reply

    Thanks, very good.

  • Don // July 3, 2008 at 3:23 pm | Reply

    Thank you very much! My situtation was different in that the dbo.sysmaintplan_log
    and dbo.sysmaintplan_subplans ‘lost’ the Name value; it was set to Null, so the script wouldn’t run.

    Even so, I was able to nuke the rows by using plan_id etc.

    You saved me alot of time, Thanks Again!

  • CliveB // August 15, 2008 at 2:39 am | Reply

    thanks for this, a great help

  • adib // September 9, 2008 at 11:30 am | Reply

    Thanks.

    This helped us with a cloned VMware server issue

  • JIM // September 9, 2008 at 3:38 pm | Reply

    CAN YOU HELP ME WITH THIS HAVING THE SAME PROBLEM

    Sql Server 2005 Delete Maintenance Plan Error

  • Godders // November 14, 2008 at 2:39 pm | Reply

    Thanks, worked great

  • Jeff // December 4, 2008 at 3:12 pm | Reply

    Thanks,

    Worked well. awsome!!

  • Mark Johnson // January 14, 2009 at 10:47 am | Reply

    Again worked very well!!! Many thanks, Mark.

  • Diesel // February 9, 2009 at 5:48 pm | Reply

    thanks, work great

  • Lennart Ljung // May 12, 2009 at 6:35 am | Reply

    YES it works, many thanks

  • Marco // June 17, 2009 at 5:14 pm | Reply

    Thanks, this helped me find the solution but mine was slightly different where the server’s IP address changed and the local server’s ip was no longer valid. Had to use some of the techniques here to delete from source tables. Crazy! Thanks for the help!

  • vineela // February 18, 2010 at 12:04 pm | Reply

    Yes it worked.. thanks

  • Jack Koh // April 21, 2010 at 3:28 pm | Reply

    Thank you so much! You’re really a genious! :D

  • Osama // May 11, 2010 at 7:21 am | Reply

    thanka alot for the amazing solution.

Leave a Comment