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.
- Disabling constraints in a system database in order to delete the record (not recommended)
- Working out the order to delete maintenance plan records from system tables in order to obey referential integrity
- Finding someone who has done it before.