sql 2005 Deleteing maintenance plans

[paulovey]

Hi,

I am trying to remove an old reference to a old maint plan that is no longer used on a new sql 2005 system.

When I try to delete the plan I get the following error


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


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)

I run the following script to try to find the problem row in the table.

Use MSDB
GO
Select * from sysmaintplan_subplans
GO

But this returns rows all with long CIDS. In the subplan_id column. So I dont know which row to delete.

So any help, with a better script to get more meaniful results will be much appreciated.

Thanks,

Paul

If aint broken, dont try n fix it!!!!!

 

[ptheriault]

Paul,
It is never a good idea to delete from a any system table. I would open the maint plan delete any and all tasks then try to delete the plan.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[oracleSQLdba]

I've heard deleting the subplan will do it for you and renaming the job will also do it.

Haven't tried either.

 

[paulovey]

Thanks for the response ptheriault and oracleSQLdba.

I have already deleted all the tasks in the maint plan.

Im very new to SQL 2005, so where would I find the subplan?

Thanks,

Paul

If aint broken, dont try n fix it!!!!!

 

[kbear]

Did you get a resolution for this? I have a job I cannot get rid of. It was just a backup. I have deleted the step and renamed it. But I still can't drop the job.