SQL SERVER 2005删除维护作业报错：The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id"
时间：2022-03-14 20:13
案例环境：
数据库版本： Microsoft SQL Server 2005 (Microsoft SQL Server 2005 - 9.00.5000.00 (X64) )
案例介绍：
对一个数据库实例做清理工作时，发现有一个很久之前禁用的数据库维护作业，于是遂删除该作业，但是删除该作业时，遇到如下错误：
脚本删除操作：
案例分析：
从错误信息我们可以看出是删除某个系统表中记录时，由于外键约束关系，导致删除失败。最后导致存储过程msdb.dbo.sp_delete_job执行失败。我想彻底弄清楚删除失败的具体原因，于是可以从提示信息的系统表dbo.sysmaintplan_subplans开始，如下所示，
可以看到系统表dbo.sysmaintplan_subplans中的job_id字段引用了msdb.dbo.sysjobs中的job_id字段，那么可以肯定是在删除msdb.dbo.sysjobs表中对应记录时，没有先删除dbo.sysmaintplan_subplans中的记录。这样推测也跟报错信息吻合。
那么接下来我们研究一下msdb数据库的存储过程[dbo].[sp_delete_job] 如下所示：
从上面SQL脚本中可以看到在删除msdb.dbo.sysjobsteps之前，该存储过程执行了msdb.dbo.sp_delete_job_references而msdb.dbo.sp_delete_job_references这个存储过程又接着调用了存储过程sp_maintplan_delete_subplan，也就是说最终在此存储过程sp_maintplan_delete_subplan中删除msdb.dbo.sysmaintplan_subplans 表中的记录。 过程梳理清楚了，那么逆向推导看看具体原因如下所示，删除msdb.dbo.sysmaintplan_subplans中对应记录语句如下
此时要看参数@subplan_id的取值，它从msdb.dbo.sp_delete_job_references中传入，如下所示
ALTER PROCEDURE [dbo].[sp_maintplan_delete_subplan]
@subplan_id UNIQUEIDENTIFIER,
@delete_jobs BIT = 1
AS
…………………………………………………………………
在[dbo].[sp_delete_job_references]中，它的值来自于 @maintplan_subplan_id变量，最终来自于sysmaintplan_subplans系统表
我通过DAC登录数据库（sysmaintplan_subplans是内部对象，此对象在DAC下才可以访问），查询如下所示，你会发现无记录，也就是说@maintplan_subplan_id为NULL值，导致后面执行删除msdb.dbo.sysmaintplan_subplans表中记录时，没有真正的删除记录。
最后发现导致查询无记录的原因在于查询条件sjv.master_server = 1
sysjob_view视图代码如下所示：
继续往下扒，视图dbo.sysoriginatingservers_view代码如下所示，原来master_server的值是默认的。因为表dbo.sysoriginatingservers无记录。至此，可以看出，这应该是SQL Server 2005的一个BUG来的。解决方案：
手工删除系统表msdb.dbo.sysmaintplan_subplans中的记录，然后删除该作业。问题搞定。
USE [msdb]GODELETE FROM msdb.dbo.sysmaintplan_subplans WHERE SUBPLAN_ID=‘B9A639EB-955D-4AE6-B69E-860145C133E7‘;USE [msdb]GOEXEC msdb.dbo.sp_delete_job @job_id=N‘ce8cb4ad-c91f-45bc-9e21-b50947063fba‘, @delete_unused_schedule=1GOSQL SERVER 2005删除维护作业报错：The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id",布布扣,bubuko.com
热门排行
今日推荐
热门手游
