Cleaning up Old Maintenance Plans

This is something I trip over every now and then on systems we inherit from our clients, and I thought I would write it up in case it helps someone else {-:

In the most recent case, I was looking at a SQL Server 2008 server that had been upgraded from SQL 2005 without installing Integration Services, so the maintenance plan jobs were failing with an error like this:

The SQL Server Execute Package Utility requires Integration Services to be installed by one of these editions of SQL Server 2008: Standard, Enterprise, Developer, or Evaluation. To install Integration Services, run SQL Server Setup and select Integration Services. The package execution failed. The step failed.

Rather than installing the relevant hotfix (http://support.microsoft.com/kb/961126) or upgrading to a service pack that they haven't tested, I took another step in my never-ending battle against SQL Server Maintenance Plans in favor of Ola Hallengren's Maintenance Solution.  If you don't already use it - check it out (all the cool kids are doing it {-:)

I installed MaintenanceSolution.sql and configured schedules comparable to the broken maintenance plans, and then went to delete the maintenance plans and their jobs:

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'.

Sigh.

I have seen this more than once for a variety of reasons, including plans that get deleted before the jobs and just flat out broken maintenance plans - but the fix is relatively easy (if you're OK deleting rows from tables in MSDB):

/*Look in the plans table to find the plan you wish to delete*/

select * from msdb..sysmaintplan_plans

/*Copy-Paste the Plan's ID (will be in GUID format) from the previous query into the where clause of the following statements and run them to clean up the foreign key relationships in the proper order*/

delete from msdb..sysmaintplan_log where plan_id = '11111111-AAAA-BBBB-CCCC-222222222222'

delete from msdb..sysmaintplan_subplans where plan_id = '11111111-AAAA-BBBB-CCCC-222222222222'

delete from msdb..sysmaintplan_plans where id = '11111111-AAAA-BBBB-CCCC-222222222222'

/*Check the plans table again to verify the plan id gone */

select * from msdb..sysmaintplan_plans

At this point the maintenance plan is gone and you can now safely (and successfully) delete the offending maintenance plan job(s) from Management Studio or from the command line.

Hope this helps!