SQL Server代理作业依赖性 [英] SQL Server Agent job dependency

问题描述

我们有一个4h数据仓库作业，按4h计划每4h运行一次。我们希望创建一个新的每日时间表，并使某些流程用完几个小时。

We have a 4h datawarehouse job that runs every 4h on 4h schedule. We want to create a new 'daily' schedule and have some processes run out of hours.

但是，如果日常作业开始运行而4h作业仍在运行，我希望它等待4h作业完成后再运行（或有指定的持续时间）。我还没有决定哪个最好。

However, if when the daily job comes to run and the 4h job is still running, I would like it to wait for the 4h job to complete, then run (or have a specified duration). I haven't decided which is best yet...

这怎么可能？谢谢！

请不要建议第三者选择，因为我无法控制基础结构。

Please do not suggest 3rd party options as I have no control over the infrastructure.

推荐答案

请在一张表中保留所有作业详细信息，包括作业状态，即进行中，已完成。每天运行作业时，您必须检查第4个工作是否成功完成，然后开始您的日常工作，并在第4个工作完成后触发您的日常工作。

Please maintain all job details in one table including job status i.e in progress, completed. When daily job run you have to check 4th job completed successfully then start your daily job and after completion of 4th job trigger your daily job.

create TABLE [dbo].[tblDailyJob](
    [JobId] [int] NOT NULL,
    [JobDesc] [varchar](500) NOT NULL,
    [JobStartTime] [datetime] NULL,
    [JobEndTime] [datetime] NULL,
    [JobStatus] [int] NOT NULL,
 CONSTRAINT [PK_tblDailyJob] PRIMARY KEY CLUSTERED 
(
    [JobId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[tblDailyJob] ADD  CONSTRAINT [DF_tblDailyJob_JobStatus]  DEFAULT ((0)) FOR [JobStatus]
GO

工作状态-0未开始，1进行中，2完成，3失败

job status- 0 Not started,1 In progress,2 Completed,3 Failed

insert into tblDailyJob values(1,'1st Job',null,null,0)
insert into tblDailyJob values(2,'2nd Job',null,null,0)
insert into tblDailyJob values(3,'3rd Job',null,null,0)
insert into tblDailyJob values(4,'4th Job',null,null,0)
insert into tblDailyJob values(5,'Daily Job',null,null,0)

在执行工作之前设置开始时间，在完成工作后设置结束时间和工作状态

set start time before excecuting job and end time and job status after completion of job

在完成第四项工作后触发您的日常工作或触发该工作手动然后检查第4个工作是否完成

after completion of 4th job trigger your daily job or if you are triggering it manually then check 4th job is completed or not

您也可以使用计划任务来完成任务。

you can also use task schedular for the same.

这篇关于SQL Server代理作业依赖性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！