The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id" | SQL

錯誤訊息

“子計劃 缷除失敗”
delete 陳述式與reference 條件的約束’FK_subplan_job_id’衝突。衝突發生在資料庫
“msdb”,資料表 “dbo.sysmaintplan_subplans”,colum “job_id”。

英文版：

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

發生原因

因為先刪除主計畫之後，再刪除子計畫時，就會找不到主計畫在哪裡，因此無法刪除。

解決步驟

Step 1：先查詢子計畫和子計畫的 log

1
2
3
4
5
6
7
8
9

USE [MSDB]
GO

-- 查詢子計畫
select * from sysmaintplan_subplans

-- 查詢子計畫log
select * from sysmaintplan_log

Step 2：刪除子計畫和子計畫的 log

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

USE [MSDB]
go

-- 刪除維護計畫子計畫的 log
DELETE FROM sysmaintplan_log
WHERE subplan_id in 
  ( SELECT Subplan_ID from sysmaintplan_subplans
    -- change Subplan name where neccessary 
  WHERE subplan_name = 'sub plan name' ) 

-- 刪除維護計畫子計畫
DELETE FROM sysmaintplan_subplans
WHERE subplan_name = 'sub plan name'

Step 3：到 SQL Agent Server 刪除該計畫即可

參考資料

The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”

MS SQL SERVER 刪除 工作排程(JOBS)時出現錯誤