Zahid's blog on Data Engineering and BI

Hello everyone,

This time my problem was to delay the start of a SQL server job if another job is already running. Obviously, the reason for this is that the jobs use the same database resources. The sysjobhistory table in MSDB database has the job ids and their status stored. First, I selected the job id which have the run_status=4 (4 means “Job in progress”) from sysjobhistory table. Then I selected the job_id and name of my suspected jobs from sysjobs table. Then, I joined these two result sets and put the joined result set inside IF NOT EXISTS command. In short, I check if any of my suspected jobs is running before I start my job. If the “IF NOT EXISTS” command returns true, I break the while loop and start my job. If it returns false, the process goes to sleep mode for 10 minutes. After 10 minutes the process wakes up and continue from the beginning of the while loop. Below is the code I have used to solve my problem:

WHILE(1=1)
BEGIN
IF NOT EXISTS
(

SELECT A.job_id FROM

(

SELECT job_id FROM msdb.dbo.sysjobhistory WHERE  run_status = 4

)A

INNER JOIN

(

SELECT job_id,name FROM msdb.dbo.sysjobs WHERE name LIKE ‘%Mirror%’
OR name LIKE ‘%BR%’
OR name like ‘%Fact%’

)B

ON A.job_id = B.job_id

)

BEGIN
BREAK;
END

WAITFOR DELAY ’10:00′

END

—-start my job——
EXEC msdb..sp_start_job @job_name = ‘My Job’