Trouble deleting SQL 2005 Maintenance Plans/Jobs

Question

Trouble deleting SQL 2005 Maintenance Plans/Jobs

Shabba

SSCertifiable

Points: 5007
More actions
August 7, 2007 at 3:49 am

#197141

I'm trying to delete several maintenance plans from SSMS and get the following error...
Exception has been thrown by the target of an invocation. (mscorlib)
An error was encountered when trying to remove the package "Maintenance Plans\User Databases - Full Daily Backup" from SQL Server.
If I try and delete the associated SQL Agent Job I get a different error...
Drop failed for Job 'User Databases - Full Daily Backup.Subplan_1'. (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 DELETE statement conflicted with the REFERENCE constraint "FK_subplan_schedule_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'schedule_id'.
The statement has been terminated.
The statement has been terminated. (Microsoft SQL Server, Error: 547)
I'm using SQL 2005 Standard Server Build 3054, what do I need to do in order to delete these plans/jobs from the msdb database? The virtual server was built a few days ago and only has a dummy database created for the purpose of testing the user database maintenance plans.
Both of the maintenace plans that I am trying to delete were initially created by exporting from one instance to another instance on the same server using SSIS.
TIA.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 1

Mark, I had the same issue last week what i did was queried the sysmaintplan_subplans and removed the entry for the maintenance plan from this system table.......then it was once agiain referencing another table i.e dbo.sysmaintplan_logs.......so you need to remove the entry for the maintenance plan from these 2 system tables which are available in msdb database

[font="Verdana"]- Deepak[/font]

Shabba SSCertifiable Points: 5007 More actions · Answer 2

Thanks Deepak, i've removed them from sysmaintplan_subplans but there are no references to these plans under sysmaintplan_log. However, they are still appearing under Maintenance Plans in SSMS.

Do you know which table/s I need to remove them from so they no longer appear in SSMS?

Cheers.

Shabba SSCertifiable Points: 5007 More actions · Answer 3

Thanks Deepak, i've removed them from sysmaintplan_subplans but there are no references to these plans under sysmaintplan_log. However, they are still appearing under Maintenance Plans in SSMS.

Do you know which table/s I need to remove them from so they no longer appear in SSMS?

Cheers.

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 4

Try deleting the jobs and it will generate an error if any this time can you try it and get back to me

[font="Verdana"]- Deepak[/font]

Shabba SSCertifiable Points: 5007 More actions · Answer 5

All the SQL Agent jobs have been removed, I just have the 2 maintenance plans listed under Management, Maintenance Plans. If I try and delete these then I get the following error.

TITLE: Microsoft SQL Server Management Studio

------------------------------

Exception has been thrown by the target of an invocation. (mscorlib)

------------------------------

ADDITIONAL INFORMATION:

An error was encountered when trying to remove the package "Maintenance Plans\User Databases - Full Daily Backup" from SQL Server.

Shabba SSCertifiable Points: 5007 More actions · Answer 6

I can see both maintenance plans listed under dbo.sysdtspackages90 not sure if I should try and delete them from this table?

Shabba SSCertifiable Points: 5007 More actions · Answer 7

Ok, having removed them from the dbo.sysdtspackages90 table they no longer appear under Maintenance Plans.

thanks deepak for you help.

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 8

Mark, Thanks for providing the solution for your problem....so that those who view this thread will be benifited...........

[font="Verdana"]- Deepak[/font]

Brett Estes-203243 SSC Journeyman Points: 76 More actions · Answer 9

I actually had the same issue after renaming a SQL Server running SQL Server 2008. Here's what I had to do:

First, I renamed the SQL Server:

1. Should see old name:

SELECT @@SERVERNAME;

GO

2. Remove that guy:

EXECUTE dbo.sp_dropserver 'SQL Server Listed Above';

GO

3. Add current server name:

EXECUTE dbo.sp_addserver 'New SQL Server', local

GO

4. Restart MSSQLSERVER Service.

5. Verify:

SELECT @@SERVERNAME;

GO

Next, I removed the job:

1. Get subplan_id from sysmaintplan_subplans and delete that data from sysmaintplan_log.

2. Get job_id from sysjobs and delete from sysmaintplan_subplans.

3. Delete the job.

Finally, I removed Maintenance Plan:

1. Delete the row from the dbo.sysssispackages table containing the maintenance plan that will not delete above.

No other jobs were having problems for me. Only the maintenance plans generated on the old server.

Rookie DBA SSC Veteran Points: 295 More actions · Answer 10

the maintenance plans details are populated among the following tables. u should delete the relevant records from all the 4 tables in order to make it vanish in SSMS. since there are relationships established, start deleting the records in the given order

sysmaintplan_logdetail

sysmaintplan_log

sysmaintplan_subplans

sysmaintplan_plans

after the records are deleted, refresh maintenance plan folder in SSMS. all the old maint plans will be vanished !!

then u can delete the relevant jobs without any error 🙂