Friday, 2 September 2011

SQL 2005 : Maintenance Plan won't update, Agent job won't delete

I'm sure this is a documented bug somewhere, but I came across a Maintenance Plan / Agent Job issue today. The client was running SQL 2005 SP2 (Build 9.00.3042.00)

I created a Maintenance Plan (Transaction Log backups), scheduled it (for every 30 minutes) and it ran fine for a few hours. I came back to it today to find it no longer running. On examining the Maintenance Plan it would not let me view the schedule, returning to SQL Agent jobs I was unable to delete the job either.

The message I got was -

Trying the same from TSQL,

delete from sysmaintplan_subplans
where subplan_description = 'TL backups'

Produced the same -

Msg 547, Level 16, State 0, Line 2 The DELETE statement conflicted with the REFERENCE constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_log", column 'subplan_id'. The statement has been terminated.

This left me we with 3 options.
  1. Disabling constraints in a system database in order to delete the record (not recommended) 
  2. Working out the order to delete maintenance plan records from system tables in order to obey referential integrity 
  3. Finding someone who has done it before.
Fortunately option 3 was just a few keystrokes away. Clay McDonald has already written a procedure and has published it in this awesome workaround : Can’t Delete Jobs (Microsoft SQL Server, Error: 547) 


Bob said...

The link to Clay's blog doesn't work. I'm sure i can find it via google if needed, but just thought you should know.

r5d4 said...

thank you Bob, have corrected :)