删除一个Job的时候，报这个错误。

Question

TITLE: Microsoft SQL Server Management Studio
------------------------------

Drop failed for Job 'FIS_BAK_notuse'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.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.4053&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

我看这个Job中没包含任何步骤，不知道是谁建立的？

现在也不能删除掉。

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

Answer 1

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)

 

这里看应该是通过维护计划建立的，通过维护计划删除吧。不能在job管理界面删除这个。

---

family as water

Answer 2

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)

 

这里看应该是通过维护计划建立的，通过维护计划删除吧。不能在job管理界面删除这个。

---

family as water

我看到是通过维护计划建立的。但是现在的问题是维护计划不在了。。。。

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

Answer 3

You should clean up sysmaintplan_* tables in msdb first.

Answer 4

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)

 

这里看应该是通过维护计划建立的，通过维护计划删除吧。不能在job管理界面删除这个。

---

family as water

我看到是通过维护计划建立的。但是现在的问题是维护计划不在了。。。。

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

在msdb.dbo.sysjobs里面找到这个job_id，然后在dbo.sysmaintplan_subplans这个表里面删除job_id相等的记录，然后就可以在job哪里删除这个记录了。

 

---

family as water

Answer 5

你好，

这是由于在msdb数据库上的两个系统表sysmaintplan_log 和 sysmaintplan_subplans 的外键冲突引起的。你可以通过以下步骤手动删除这个工作:

首先，请备份msdb数据库。

然后，执行下面的手动执行以下语句：

use msdb
go
 
 
 begin tran 

 
 --根据工作名称找到工作id 和维护计划id
select subplan_id from dbo.sysmaintplan_subplans where job_id = (select job_id from sysjobs where name='jobname' )
 
 
-- 删除对应的维护计划日志记录
delete from dbo.sysmaintplan_log where subplan_id = (select subplan_id from dbo.sysmaintplan_subplans where job_id = (select job_id from sysjobs where name='jobname' ))
 

--删除维护计划记录
 
delete from dbo.sysmaintplan_subplans where job_id = (select job_id from sysjobs where name='jobname' )
 

--删除工作记录
 
delete from dbo.sysjobs where job_id = (select job_id from sysjobs where name='jobname' )
 
 
 
 commit tran
 
 

 

 

 

---

Best Regards,
Stephanie Lv