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'
Advertisements

6 Responses to The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id"

  1. mumzi says:

    Hi

    I’d like to modify the sub plan fro command line . Is it possible ?

    I’m looking to change the backup path on DB backup maintenance plan

    thanks
    mumzi

  2. Glen says:

    Reblogged this on WIKIPinoy and commented:
    This post helped me with my issue on one of my jobs. Thanks

  3. Alex Nuta says:

    This solved my problem.
    Well done and thanks.

  4. Dave says:

    Solved my problem too. Thank you!

  5. Big Al says:

    Thanks! Fixed my problem

  6. Pingback: The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id” | sqlriley

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: