Advertisements

Archive

Archive for the ‘Maintenance Plans’ Category

"New Maintenance Plan…" doesn’t work

March 24, 2009 Leave a comment

For some reason Microsoft decided not to enable that function in some editions of RTM in SQL server 2008 (annoying but meh). So looking around I found Microsoft Connect article, link. According to that it was suppose to be for the IA64 only but I was only running developer edition on x86 Windows XP Sp3…

What made it more annoying was you don’t get any error messages or nothing indicating it is not going to work. But I was able to use the wizard to create the maintenance plan; heh guess they over looked that.

So anyhow I decided to install CU4 and I got my Maintenance Plans back again…

CU4 download link; note you have to specially request it.

Advertisements

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

March 8, 2009 1 comment

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
%d bloggers like this: