Jigsaw Blog

24

Nov
2010
0 comments

SQL Maintenance plan won't run, modify or delete

We recently replaced on SQL server with another. Ths involved server renaming and some of the Maintenance Jobs stopped running. We were unable to delete them through SQL management Studio. They can be manually "adjusted" and then can be deleted. The issue was that the Server name they referred to no longer existed.

  1. Change to the msdb Database
  2. Find the ID of the Job using the select statement
    select * from sysmaintplan_plans

  3. Replace the X in the statements below with the ID from Step 2 and run the delete statements
    delete from sysmaintplan_log where plan_id = 'X'

    delete from sysmaintplan_subplans where plan_id = 'X'

    delete from sysmaintplan_plans where id = 'X'

  4. Delete the SQL Server Jobs with the Management Studio