The DELETE statement conflicted with the REFERENCE constraint FK_subplan_job_id
Issue: When trying to delete a Scheduler job which was created as part of a maintenance plan, we get the following error
TITLE: Microsoft SQL Server Management Studio —————————— Drop failed for Job ‘manual_db_backups.Subplan_1’. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476 —————————— ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) —————————— 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) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476
We were getting the error even after removing the maintenance plan from SSIS.
To Resolve I did the following:
USE [MSDB]
GO
— View the Maintenance plan subplans
select * from sysmaintplan_subplans
— View the Maintenance plan logs
select * from sysmaintplan_log
To Delete the subplan:
USE [MSDB]
go
–Delete the Log history for the maintenance plan affected
DELETE FROM sysmaintplan_log
WHERE subplan_id in
( SELECT Subplan_ID from sysmaintplan_subplans
— change Subplan name where neccessary
WHERE subplan_name = ‘Subplan_1’ )
— Delete the subplan
DELETE FROM sysmaintplan_subplans
WHERE subplan_name = ‘Subplan_1’