Its happen, sometime we delete maintenance plan from SSMS, due to some reason it show succeed or error and in between that some object/steps being deleted but some not, sometimes you see maintenance plan job name in Job Activity Monitor/Jobs Listing and when you tr to delete that job, its through error like

Drop failed for Job ‘XXXXXXXXXXXXX’. (Microsoft.SqlServer.Smo)The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id(xxx)”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’.The statement has been terminated. (Microsoft SQL Server, Error: 547)

We can resolve this error as

  1. Find which plans needs to delete by :
    SELECT * FROM sysmaintplan_plans
  2. Using above query, you can get plane ID of your maintenance plan which you want to delete
    DELETE FROM sysmaintplan_log WHERE plan_id = ‘Plan ID of your needs to be delete Maintenance Plan’
    DELETE FROM sysmaintplan_subplans WHERE plan_id = ‘Plan ID of your needs to be delete Maintenance Plan’ 
    DELETE FROM sysmaintplan_plans WHERE id = ‘Plan ID of your needs to be delete Maintenance Plan’

    Just Delete entries from above mentioned these 3 tables and be Happy from such problem!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s