Cannot Delete Maintenance Plans - SQL 2005

A consultant created a maintenance plan that isn't in use and is not
enabled. I want to delete the plan but get the following error.
TITLE: Microsoft SQL Server Management Studio
Drop failed for Job 'Maintenance II'. (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdN...b&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?ProdN...7&LinkId=20476
BUTTONS:
OK
I"m such a noob, how can i delete this plan?
tia,
Randy
>From what I can tell from the error msg, the job you are trying to
delete has a subplan that references it and you can't delete it until
you get rid of the subplan.
How I would do this:
1.) get job id #
2.) look up job id# in the system table in question
3.) find the subplan for the job id #
4.) delete subplan
4.) delete job
proceed with caution and don't delete anything unless 100% sure !
you should back up your jobs first, too in case something blows up
perhaps there is an easier way to do this through the menus in
Management Studio
Randy H wrote:
> A consultant created a maintenance plan that isn't in use and is not
> enabled. I want to delete the plan but get the following error.
> TITLE: Microsoft SQL Server Management Studio
> --
> Drop failed for Job 'Maintenance II'. (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...b&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?ProdN...7&LinkId=20476
> --
> BUTTONS:
> OK
> --
> I"m such a noob, how can i delete this plan?
> tia,
> Randy
|||Is there an easier way through the menus?
I am not having any luck finding the sub plan for the job id #
<tootsuite@.gmail.com> wrote in message
news:1160078499.215907.169040@.k70g2000cwa.googlegr oups.com...
> delete has a subplan that references it and you can't delete it until
> you get rid of the subplan.
> How I would do this:
> 1.) get job id #
> 2.) look up job id# in the system table in question
> 3.) find the subplan for the job id #
> 4.) delete subplan
> 4.) delete job
> proceed with caution and don't delete anything unless 100% sure !
> you should back up your jobs first, too in case something blows up
> perhaps there is an easier way to do this through the menus in
> Management Studio
>
> Randy H wrote:
>
|||Here is the SQL to fix this:
http://www.gudenas.com/2007/04/20/sql-server-2005-delete-maintenance-plan-error/
From http://www.developmentnow.com/g/118_2006_10_0_0_831628/Cannot-Delete-Maintenance-Plans--SQL-2005.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com