Maintenance Plan

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 the below 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 the BACK button all the way back to 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