Cannot remove job

• Chris Chilvers

  SSCrazy

  Points: 2260

  More actions

  January 8, 2007 at 4:06 am

  #117052

  Hi, I'm having a little trouble removing an old job that was created along side a maintenance plan. The maintenance plan itself is removed but trying to remove the job yeilds the 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'.

  1) Before I go around deleting things from the system tables I was trying to check if this plan was referred to by anything else and I noticed that the column 'plan_id' doesn't seem to relate to anything within the database, if this is the case, what does it relate to?

  2) Is there a stored procedure I should be running instead of removing from the system tables myself?

• January 11, 2007 at 8:00 am

  This was removed by the editor as SPAM

• IanR

  SSC Eights!

  Points: 998

  More actions

  January 11, 2007 at 11:56 am

  #682743

  One thing I have seen is when the job is associated with a Maintenance procedure it will error if you try to delete the job before deleting the Maintenance Plan. 

  ---

  New to the DBA world...thank you for your help!,

  IanR
  

• MohammedU

  SSCoach

  Points: 19644

  More actions

  January 11, 2007 at 1:34 pm

  #682763

  Check the system table sysmaintplan_subplans and delete if any data related to your job exists in that table...

  SELECT s.subplan_id

  ,s.subplan_name

  ,s.subplan_description

  ,s.plan_id

  ,s.job_id

  ,s.schedule_id

  ,j.name

  FROM msdb.dbo.sysmaintplan_subplans s

  LEFT OUTER JOIN msdb.dbo.sysjobs j

  ON s.job_id = j.job_id

   

  MohammedU
  Microsoft SQL Server MVP

• Chris Chilvers

  SSCrazy

  Points: 2260

  More actions

  January 12, 2007 at 3:30 am

  #682839

  So this is one of those cases where you have to play with the system tables.

  But what does job_id relate to? Is it the package ID in SSIS?