DELETE 语句与 REFERENCE 约束"FK_subplan_job_id"冲突。该冲突发生于数据库"msdb"，表"dbo.sysmaintplan_subplans", column 'job_id'。 语句已终止。 (Microsoft SQL Server，错误: 547)

删除 对于 Job“xxxxxxx.Subplan_1”失败。  (Microsoft.SqlServer.Smo)
有关帮助信息，请单击: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2548.0+((SQL14_RTM_QFE-CU).150608-1029)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=删除+Job&LinkId=20476
------------------------------
其他信息:
执行 Transact-SQL 语句或批处理时发生了异常。 (Microsoft.SqlServer.ConnectionInfo)
-----------------------------
DELETE 语句与 REFERENCE 约束"FK_subplan_job_id"冲突。该冲突发生于数据库"msdb"，表"dbo.sysmaintplan_subplans", column 'job_id'。
语句已终止。 (Microsoft SQL Server，错误: 547)
有关帮助信息，请单击: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2548&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476
------------------------------
按钮:
确定
------------------------------

 

use [msdb]
declare @job_name varchar(100)
set @job_name = N'xxxxxx.Subplan_1'
--注：jobName为维护计划对应的jobName
--删除在计划里面的日志 
delete sysmaintplan_log from sysmaintplan_subplans as subplans
inner join sysjobs_view as syjobs on subplans.job_id = syjobs.job_id
inner join sysmaintplan_log on subplans.subplan_id =sysmaintplan_log.subplan_id
where (syjobs.name = @job_name)
--删除代理的作业
delete sysjobschedules from sysjobs_view v
inner join sysjobschedules o on v.job_id=o.job_id where v.name=@job_name
--删除子计划
delete sysmaintplan_subplans from sysmaintplan_subplans as subplans
inner join sysjobs_view as syjobs on subplans.job_id = syjobs.job_id
where (syjobs.name = @job_name)
--删除作业
delete from msdb.dbo.sysjobs_view where name = @job_name

 

--查询有哪些作业

select * from msdb.dbo.sysjobs_view

 

参考

https://www.cnblogs.com/xiaoyou2018/p/18128310