SQL Server - cannot drop idle job

Question

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?

Answer 1

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.

Answer 2

The problem appears to be that you have a maintenance plan that has created these jobs. As such you will not be able to delete them simply by deleting the job.

What you will need to do is identify which maintenance plan is associated with the jobs and then delete the maintenance plan all together. This should then either remove the jobs or allow you to do it.

The following query should help you to identify the associated maintenance plan.

select 
    p.name as 'MaintenancePlan'
    ,sp.subplan_name as 'SubplanName'
    ,j.name as 'Job Name'
    ,j.[description] as 'Job Description'  
from msdb..sysmaintplan_plans p
    inner join msdb..sysmaintplan_subplans sp
    on p.id = sp.plan_id
    inner join msdb..sysjobs j
    on sp.job_id = j.job_id

Answer 3

--
-- Clean up orphaned Maintenance Plan subplans and their logs
-- Use this if you get a conflict error deleting a job
--

use [msdb]
GO

delete from msdb..sysmaintplan_log
where plan_id not in (
select plan_id from msdb..sysmaintplan_plans);
GO

delete from msdb..sysmaintplan_subplans
where plan_id not in (
select plan_id from msdb..sysmaintplan_plans);
GO