标题: Microsoft SQL Server Management Studio ------------------------------ 删除 对于 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/DBArtist/p/subplan_job.html
https://blog.csdn.net/qq_37579133/article/details/133686400
