Query SQL agent job execution status

Question

Greetings!!!

Hi, I have a few jobs that I'm working on sequencing based on dependency. I have a parent job that kick-starts 5 different child jobs. The next step is to make sure all the jobs specified in previous step completed either successfully or failed. Now the challenge is each one of those 5 jobs takes duration to complete. Let's take this hypothetical situation. Now the challenge is I don't know how long each job might take in the future and just can't hard-code some random number that I'll have to change time and again as the data volume keeps increasing.

This below table is built looking at the average time each job takes so far.

Job Name	Execution Time
ChildJob1	3 minutes
ChildJob2	1 minute
ChildJob3	12 minutes
ChildJob4	7 minutes
ChildJob5	8 minutes

SQL:

The challenge I'm running into is when I loop on this query once every 2 minutes it's getting stuck in an infinite loop and never wants to complete. If I were to wait for a certain time that would not scale well. How would I check the job execution status so that I can kick-start down stream jobs based on up stream job status.

DECLARE @jobname VARCHAR(MAX) = 'ChildJob1,ChildJob2,ChildJob3,ChildJob4,ChildJob5';
DECLARE @jobnames TABLE (name VARCHAR(1000));

INSERT INTO @jobnames (name)
SELECT TRIM(value)
FROM STRING_SPLIT(@jobname, ',');

DECLARE @waitTimeInSeconds INT = 30;-- Adjust wait time as needed
DECLARE @waitTimeFrequency VARCHAR(8);

WHILE 1 = 1
BEGIN	   

	DECLARE @runningJobs INT = (
			        SELECT COUNT(*)        
			FROM msdb.dbo.sysjobactivity a        
			INNER JOIN msdb.dbo.sysjobs j ON a.job_id = j.job_id        
			WHERE j.name IN (
					SELECT name
					FROM @jobnames
					)            
				AND a.start_execution_date IS NOT NULL            
				AND a.stop_execution_date IS NULL    
			);

	   

	IF @runningJobs = 0    
	BEGIN
		       

		PRINT 'All specified jobs are complete.';

		       

		BREAK;

		   
	END;

	   

	SET @waitTimeFrequency = '00:' + RIGHT('00' + CAST(@waitTimeInSeconds / 60 AS VARCHAR(2)), 2) + ':' + RIGHT('00' + CAST(@waitTimeInSeconds % 60 AS VARCHAR(2)), 2);

	   

	PRINT 'Waiting for ' + @waitTimeFrequency + '... Some specified jobs are still executing.';

	   

	WAITFOR DELAY @waitTimeFrequency;
END;

Answer 1

Option 1

Put these workloads (sql scripts) as steps in a single job. They will follow the dependency of each step. all steps will run in sequence on completion of previous step.

Option 2

Last step of each job is a call to start next job. By doing this all jobs will follow a sequence and will only start on completion of dependent step.