CODEWITHSUNDEEP
sql-serversql-server-2012database-administration
jhowe
jhowe

Reputation: 10828

SQL Server Agent job dependency

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.

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...

How is this possible? Thanks!

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

Upvotes: 0

Views: 1558

Answers (2)

yatin parab
yatin parab

Reputation: 174

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

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

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.

Upvotes: 1

Ben Thul
Ben Thul

Reputation: 32707

Take a look at sp_getapplock. Essentially, you can create your own lock that obeys all of the normal SQL Server locking semantics. In your case, you can say "wait until I can obtain this lock before continuing".

Upvotes: 1

Related Questions