I have deleted a maintenance plan it appears that the job for that maintence plan has been left behind. When I attempt to delete the job I get thebelow message. Any idea how to remove this orphaned job?

TITLE: Microsoft SQL Server Management Studio
------------------------------

Drop failed for Job 'User DB Maintenance Plan'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

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)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

i have tested this and when i delete the maintenance plan via Mgmt Studio (2047/sp1) it also removes the corresponding job. However, I have produced this effect you are describing in the past. What build of Mgmt Studio are you using?

Michael,

Run this query against your server and see if your maintence plan is in the database:

Use MSDB
GO
Select * from sysmaintplan_subplans
GO

If you see it there, pick a column like the name of your maintenence plan and fill in the Where statement below and run it to see if you can delete it

Use MSDB
GO
Delete from sysmaintplan_subplans
Where name = 'myplan'
GO

If you get an error with an FK like you did before, there is another table that has a relationship with this table, you'll need to detemine the other table name and query it and try to delete the relationship of the plan from there and then go back to the sysmaintplan_subplans table.

I had to do something similar to this before and I was able to get it out by using this approach.

I have the same problem. It was created by a bug in the maintenance plans wizard.

As I was about to complete the wizard's process to build a maintenance plan, I clicked theBACK button all the way backto the first wizard dialog. After that I could not complete the wizard process because something had been created. Therefore the wizard got confused leaving this problem, which I cannot delete the job which has no associated maintenance plan.

ktmd

There was a foreign key constraint on the sysmaintplan_log. I deleted the item with the contraint in the sysmaintplan_log table and that did the trick.

delete from dbo.sysmaintplan_log where plan_id = 'my_plan_id '

Thank you.

Hi Michael-

Your situation is exactly what has happened to me. I realize your posted the fix, but I am a SQL newbie and do not know where/how to run the 'delete from dbo.sysmaintplan.log where plan_id = "my_plan_id'. I am currently running SQL 2005 standard. Thanks in advance.

Thank you Michael and Andy_T. I had the same problem, I'm a SQL n00b and this has been bugging me for weeks now. Your posts helped me delete my unused maintenance plan.

Thanks!!!

Thanks for the postings all!!! A year later these postings are still amazingly helpful!!!

One tip, for those who couldn't find the plan name you can use the plan id to delete:

GO

Delete from sysmaintplan_subplans

Where plan_id = 'plan id'

GO

This worked great for me.

One item to note in my case, the subplan_name was aptly titled "Subplan" for three of the query results. I knew the problematic plan was one of my first maintenance plans created on the serverand so I divined the right subplan by usingd the schedule_id number to identify it (I assume it is a sequential number). I don't know if my assumption is correct, but I did fix my problem with everyone's suggestions--so thanks!

This worked fine for me thanx !

This worked fine for me. Thanks.

Very helpful. Thank you!!

Guys, On SQL2005, I had the same problem. Based on the previous Tips, I have cleaned up the Job Activity monitor.

Here is the steps followed.

1) Prefix the name of all the Jobs that you are having trouble deleting with 'DEL'

using Job Activity Monitor

2)

use msdb

go

-- Check whether the next Delete Statment deletes only the jobs you want to delete

select * from sysjobs where name like 'DEL%'

go

3)-- Run the following SQL to delete

delete from sysmaintplan_subplans

where job_id in(

select job_id from sysjobs where name like 'DEL%')

go

4) Go to Job Activity Monitor and Right Click to Delete Jobs

Cheers,

JamesPJ

In my casewhen I trie to delete from the sysmaintplan_subplans table I got another constraint problem from

msdb.dbo.sysmaintplan_log table so I had to clean the records from sysmaintplan_log first. You can do this by using the plan_id and subplan_id because syymaintplan_subplans table gives you the necessary info. Then hit the sysmaintplan_subplans table to delete and finally you can delete the job.

Bulent

very good

This worked in a case where I exported the plans from a previous SQL installation and attempted to import them into a new installation. The plans were invalid at that point (not sure why).

JamesPJ

 

Thanks for the step-by-step!  This one was bugging me.

This info was quite helpful although I was having trouble identifying the orphaned job from the sysmaintplan_subplans table.  I was able to find a corresponding entry in the sysjobs with a matching job_id and the name of the orphaned job. 

I did encounter another contstraint conflict because the job_id also existed in the sysmaintplan_log table but once I identified the job_id I was able to delete it from the sysmaintplan_log, sysmaintplan_subplans tables followed by the sysjobs table.

Thanks.

I have SQL Server 2008 and this one worked, Thank you JamePJ

--1) Prefix the name of all the Jobs that you are having trouble deleting with 'DEL' using Job Activity Monitor
--2) v_Backup_internal_logi_Full
use msdb
-- Check whether the next Delete Statment deletes only the jobs you want to delete
select * from sysjobs where name like 'v_Backup_internal_%'

--3) Run the following SQL to delete
delete from sysmaintplan_subplans
where job_id in(
select job_id from sysjobs where name like 'v_Backup_internal_%')

--4) Go to Job Activity Monitor and Right Click to Delete Jobs

Awesome JamesPJ, thanks it worked!!

Tanx for this way

this is correctly answer