Can’t Modify/Delete Maintenance Plans in SQL 2005 after Rename
After renaming SQL Server 2005 server the maintenance plans couldn’t be deleted. When trying to delete or modify a plan user gets an error. Using the SSMS there is no way to delete the plan. I thought it might a similar issue to I had encountered with SQL Server 2000 Jobs; so I searched to see if Maintenance plans table was storing server name and couldn’t find anything. Tracy found Microsoft Connect Article, Link indicating this is a bug. But Tracy found a work around that will allow you to delete the old plans (please use with cautious as it is modifying system tables):
1)Manually delete Maintenance Plan from MSDB using script below.
2)Delete the SQL Server Jobs with the Management Studio.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
USE [msdb] GO DECLARE @PlanID AS VARCHAR(255) BEGIN TRAN DeleteOldMaintenancePlans SELECT @PlanID = id FROM sysmaintplan_plans WHERE name LIKE 'MaintenancePlan Name' DELETE FROM sysmaintplan_log WHERE plan_id = @PlanID DELETE FROM sysmaintplan_subplans WHERE plan_id = @PlanID DELETE FROM sysmaintplan_plans WHERE id = @PlanID IF @@ERROR = 0 COMMIT TRAN DeleteOldMaintenancePlans ELSE ROLLBACK TRAN DeleteOldMaintenancePlans GO |