Wednesday, April 10, 2019

How to hold or disable a SQL Agent Job until other SQL Agent Job to complete

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

No comments:

Post a Comment