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

sql

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

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

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

Maintainance Plan - Transaction Log Backups Not Deleted

Hi
Ive got a Maintainance Plan set up for all my databases.
The Database backup part works fine but I have a problem
with the Transaction log backup. I have set it to delete
old TRN files after 4 days. However it does not to this.
It creates a new TRN file, but does not delete the old
ones. I have looked in the job history - it just says the
job failed. I have checked the NTFS permissions - they
seem ok. Any ideas
TIA
RobProblem is that one of your databases recovery model is
set to simple. (microsoft knowledge base article 303292)
go to enterprise manager| click down to databases| right
click on each database in your maintenance plan | select
properties| select options| each one should NOT be simple
select either full or bulk
>--Original Message--
>Hi
>Ive got a Maintainance Plan set up for all my databases.
>The Database backup part works fine but I have a problem
>with the Transaction log backup. I have set it to delete
>old TRN files after 4 days. However it does not to this.
>It creates a new TRN file, but does not delete the old
>ones. I have looked in the job history - it just says
the
>job failed. I have checked the NTFS permissions - they
>seem ok. Any ideas
>TIA
>Rob
>.
>|||You pretty much just get that generic message when you use
the maintenance plans. To find the specific errors, you need
to go to the maintenance plans, right click on the plan that
is failing and select Database Maintenance Plan History.
The problem with deleting the backups xx days old is most
likely related to one of the issues that Allan Mitchell
listed for you in his post - permissions or a sharing
violation.
-Sue
On 22 Sep 2003 04:36:22 -0700, rss@.abasoft.co.uk (Rob Smith)
wrote:
>Thanks for that tip but, each database was set to Full
>In the step detail in job history I get this
>sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
>Rob
>
>"bob schwartz" <robtschwartz@.sbcglobal.net> wrote in message news:<01c801c37c7f$411100b0$a401280a@.phx.gbl>...
>> Problem is that one of your databases recovery model is
>> set to simple. (microsoft knowledge base article 303292)
>> go to enterprise manager| click down to databases| right
>> click on each database in your maintenance plan | select
>> properties| select options| each one should NOT be simple
>> select either full or bulk
>>|||I had tried Allans advice, but it didnt seem to make a difference who
was running it. In the end I have got it working by going into SQL
Agent/Jobs/Transaction Log Backup/Properties/Steps and changing the
database from master to one of my user DBs. This ran ok with no errors
and removed the old files.
Thanks to everyone who helped with this
Rob
Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<8n2vmvk74vhupjjfj9h96lrjpn0kcsvvd0@.4ax.com>...
> You pretty much just get that generic message when you use
> the maintenance plans. To find the specific errors, you need
> to go to the maintenance plans, right click on the plan that
> is failing and select Database Maintenance Plan History.
> The problem with deleting the backups xx days old is most
> likely related to one of the issues that Allan Mitchell
> listed for you in his post - permissions or a sharing
> violation.
> -Sue
> On 22 Sep 2003 04:36:22 -0700, rss@.abasoft.co.uk (Rob Smith)
> wrote:
> >Thanks for that tip but, each database was set to Full
> >
> >In the step detail in job history I get this
> >
> >sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
> >
> >Rob
> >
> >
> >"bob schwartz" <robtschwartz@.sbcglobal.net> wrote in message news:<01c801c37c7f$411100b0$a401280a@.phx.gbl>...
> >> Problem is that one of your databases recovery model is
> >> set to simple. (microsoft knowledge base article 303292)
> >> go to enterprise manager| click down to databases| right
> >> click on each database in your maintenance plan | select
> >> properties| select options| each one should NOT be simple
> >> select either full or bulk
> >>
> >>