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]


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


One comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.