Advertisements
Home > Maintenance Plans, SQL 2005 > Can’t Modify/Delete Maintenance Plans in SQL 2005 after Rename

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
Advertisements
  1. October 28, 2010 at 1:55 pm

    Works perfectly for a maintenance plan that has an old "Local server connection" and could therefore not be deleted in the usual way anymore.Thanks

  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: