Can’t Modify/Delete Maintenance Plans in SQL 2005 after Rename

Credit: Tracy (SQL Server Central, Link)

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 (link); 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.

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