There are instances that one job might cause issues with another job that runs parallel. In this kind of situation, it is better to hold or disabled the job until other jobs are completed.
Step 1: Disable the job
sp_update_job is used for changes the attributes of a job
@enabled - Specifies whether the job is enabled (1) or not enabled (0)
EXEC dbo.sp_update_job
@job_name = @job_name_1
@enabled = 0;
Step 2: Wait to finish already running job
WHILE EXISTS (
SELECT 1
FROM msdb.dbo.sysjobactivity
JOIN msdb.dbo.sysjobs
ON sysjobactivity.[job_id] = sysjobs.[job_id]
WHERE sysjobactivity.[session_id] = (
SELECT TOP 1 [session_id]
FROM msdb.dbo.syssessions
ORDER BY [agent_start_date] DESC)
AND sysjobactivity.[start_execution_date] IS NOT NULL
AND sysjobactivity.[stop_execution_date] IS NULL
AND sysjobs.[name] = @job_name_2
)
BEGIN
WAITFOR DELAY '00:00:15'
END
Step 3: Once the job is completed, you can re-enable the job
EXEC dbo.sp_update_job
@job_name = @job_name_1
@enabled = 0;
Cheers!
Uma
