Unable To Drop SQL Server Agent Jobs or Maintenance Plan

 

Unable To Drop SQL Server Agent Jobs or Maintenance Plan

 

Drop failed for Job job_name.Subplan_1. (Microsoft.SqlServer.Smo)

 

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

 

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)

 

You have maintenance plans. Try to follow these steps:

Find the maintenance plan name and id that you want to delete. Write down the id of the one you want to delete.

SELECT name, id FROM msdb.dbo.sysmaintplan_plans

--Place the id of the maintenance plan you want to delete into the below query to delete the entry from the log table:

    DELETE FROM msdb.dbo.sysmaintplan_log WHERE plan_id = ''

--Place the id of the maintenance plan you want to delete into the below query and delete the entry from subplans table:

    DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE plan_id = ''

--Place the id of the maintenance plan you want to delete into the below query to delete the entry from the plans table:

    DELETE FROM msdb.dbo.sysmaintplan_plans WHERE id = ''

 

Now you can delete the jobs from Management Studio.