Cannot remove/delete SQL Server job

• WC_Admin

  SSChasing Mays

  Points: 651

  More actions

  April 2, 2018 at 1:14 pm

  #410379

  I have a maintenance plan that was removed and it won't delete from the SQL Server Agent Jobs. When I try to remove the jobs I get this error:

  "Msg 547, Level 16, State 0, Procedure sp_delete_job, Line 182
  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."

  I did tones of Google searches and everytime I think I got the right answer (the query returned successful with rows effected), I would refresh the Jobs folder and see it was still there.

  I ran the following queries/scripts:
  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
  ------------------------------------------------------------------------------
  'SELECT job_id from msdb.dbo.sysjobs where name = 'Mar2014_MonthBU.Subplan1' (this is the name of the job I want to delete).
  'DELETE from msdb.dbo.sysmaintpaln_subplans where job_id = (insert job id from above query).
  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'MyJob')
  EXEC msdb.dbo.sp_delete_job @job_name=N'MyJob', @delete_unused_schedule=1
  GO
  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  SELECT job_id, name, enabled, owner_sid, date_created, date_modified FROM msdb.dbo.sysjobs where name = 'MyJobNameHere'
  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Then finally, I ran this:
  USE [MSDB]

  go

  --Delete the Log history for the maintenance plan affected
  DELETE FROM sysmaintplan_log
  WHERE subplan_id in
  ( SELECT Subplan_ID from sysmaintplan_subplans
    -- change Subplan name where neccessary
  WHERE subplan_name = 'Subplan_1' )

  -- Delete the subplan
  DELETE FROM sysmaintplan_subplans
  WHERE subplan_name = 'Subplan_1'
  ------------------------------------------------------------------------------------------------------------
  After all that, the script returned "successful" but the job is still there. I tried to remove the job again, and got the same error. I tried creating the maintenance plan again, but SQL wouldn't let me because of they use the same name.  At this point I figured it was hopeless and created a new maintenance plan with a different name. The problem was it was the wrong one and needed to remove it. I could remove it from the Management folder, but not from the SQL Server Agent Jobs. So now I'm stuck with 2 jobs I cannot remove. I did everything but reboot. Usually whenever I need to remove a maintenance plan, I remove the plan first, then the job, refresh and its gone. I've never seen anything like this before. And why is it telling me successful/rows effected when the SQL Jobs Agent says otherwise?

  Thanks.

• WC_Admin

  SSChasing Mays

  Points: 651

  More actions

  April 3, 2018 at 5:48 am

  #1985269

  Lj Burrows - Tuesday, April 3, 2018 5:18 AM

  There could be various reasons of getting SQL Server error 547.  The error occured when your child table does not match with the parent table.Go through the following link :
  http://www.sql-server-helper.com/error-messages/msg-547-delete.aspx

  Well this is odd...I didn't do anything else, went into SSMS just now, and was able to delete both maintenance plans. No errors. Could be a timing thing, but I'm all set now on this.

  Thanks.