Remove maintenance plans left after a server rename.

Many times when upgrading SQL Server or migrating to new hardware, it is necessary to rename the server when it’s ready to be used as production.  However, if any maintenance plans created before the server was renamed are still present after the rename, you may find that trying to delete the old jobs results in error 547 (below).

Drop failed for Job ‘User Databases – Full Daily Backup.Subplan_1’. (Microsoft.SqlServer.Smo)

The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’.
The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_schedule_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘schedule_id’.
The statement has been terminated.
The statement has been terminated. (Microsoft SQL Server, Error: 547)_________________________________________________________________________

Here are the steps to correct the problem:

— Find the maintenance plan name and id that you want to delete.

— Write down the id of the one you want to delete.

SELECT name, id FROM msdb.dbo.sysmaintplan_plans

— Place the id of the maintenance plan you want to delete

— into the below query to delete the entry from the log table

DELETE FROM msdb.dbo.sysmaintplan_log WHERE plan_id = ‘<<id from 1st query>>’

— Place the id of the maintenance plan you want to delete

— into the below query and delete the entry from subplans table

DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE plan_id = ‘<<id from 1st query>>’

— Place the id of the maintenance plan you want to delete

— into the below query to delete the entry from the plans table

DELETE FROM msdb.dbo.sysmaintplan_plans WHERE id = ‘<<id from 1st query>>’

Now you can delete the jobs from Management Studio.