Deleting a SQL Server maintenance plan….

If you keep getting this error:
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 statement has been terminated. (Microsoft SQL Server, Error: 547)

RUN:
(1)
Use MSDB
GO
Delete from sysmaintplan_subplans
Where subplan_name = ‘xyz’
GO

If theres a reference error to sysmaintplan_log, first get the subplan_id
(2)
SELECT
[subplan_id]
–,[subplan_name]
–,[subplan_description]
–,[plan_id]
–,[job_id]
–,[schedule_id]
–,[msx_job_id]
–,[msx_plan]
FROM [msdb].[dbo].[sysmaintplan_subplans]

then RUN:
Use MSDB
GO
Delete from sysmaintplan_log
Where subplan_id = ‘0805BDF5-4A25-4175-8D3E-D6B041714D30’
GO

(above subplan_id was from my server)

Then run query #1 again.

Leave a Reply