The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id"
December 13, 2011 6 Comments
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'
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
Reblogged this on WIKIPinoy and commented:
This post helped me with my issue on one of my jobs. Thanks
This solved my problem.
Well done and thanks.
Solved my problem too. Thank you!
Thanks! Fixed my problem
Pingback: The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id” | sqlriley