August 7, 2007 at 3:49 am
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.
August 7, 2007 at 4:01 am
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]
August 7, 2007 at 4:52 am
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.
August 7, 2007 at 4:52 am
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.
August 7, 2007 at 4:58 am
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]
August 7, 2007 at 5:30 am
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.
An error was encountered when trying to remove the package "Maintenance Plans\User Databases - Full Daily Backup" from SQL Server.
August 7, 2007 at 5:38 am
I can see both maintenance plans listed under dbo.sysdtspackages90 not sure if I should try and delete them from this table?
August 7, 2007 at 6:18 am
Ok, having removed them from the dbo.sysdtspackages90 table they no longer appear under Maintenance Plans.
thanks deepak for you help.
August 7, 2007 at 7:48 am
Mark, Thanks for providing the solution for your problem....so that those who view this thread will be benifited...........
[font="Verdana"]- Deepak[/font]
March 4, 2009 at 8:49 am
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.
March 17, 2011 at 3:16 am
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 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply