积极答复者
删除一个Job的时候,报这个错误。

问题
-
TITLE: Microsoft SQL Server Management Studio
------------------------------Drop failed for Job 'FIS_BAK_notuse'. (Microsoft.SqlServer.Smo)
------------------------------
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.
答案
-
你好,
这是由于在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
- 已标记为答案 WeiLin QiaoModerator 2011年6月28日 9:57
全部回复
-
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 -
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. -
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 -
你好,
这是由于在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
- 已标记为答案 WeiLin QiaoModerator 2011年6月28日 9:57