SQL Server - cannot drop idle job

Currently, I am transitioning a server from a company to another.

They want to keep SQL Server installed on the server so we are wiping clean all the databases, maintenance plans and jobs.

I'm trying to delete a bunch of SQL Server Agent jobs but even though they are disabled they are still on "IDLE" mode so it raises me the following error when trying to delete them:


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)

How can I remove the IDLE status from the job so the delete gets through?

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.

StackExchange Database Administrators Q#121877, answer score: 12