Handling Job Dependency in SQL Server

We might have came across a scenario where the Sql server package execution is dependent on some other job to kick start. So we need to handle the dependent nature of the job in a smart way. The dependent job might be a frequently running one or it might be overnight job, so the challenge in frequently running job is our package should not start when the dependent job is in running state.

The solution for this use case lies in msdb(msdb) a system database available in Microsoft Sql Server. This db maintains all information about sql job agent and many more information regarding transaction and other audit details.

I have come up with a reusable T-SQL stored procedure which does this task for us without much manual interaction. We can call this stored proc in the first job step and this sp will keep checking for dependent job to either complete or will check for latest successful run for current execution day.

USE Testdb
GO

IF EXISTS (
  SELECT *
  FROM SYS.OBJECTS
  WHERE TYPE = 'P'
   AND NAME = 'udsp_Dependent_Job_Status_Check'
  )
 DROP PROCEDURE udsp_Dependent_Job_Status_Check

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_WARNINGS ON
GO
SET ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO

CREATE PROC [dbo].udsp_Dependent_Job_Status_Check @PKG_NAME VARCHAR(100), @DEP_JOB_NAME VARCHAR(100), @USAGE INT
AS
/*
CREATED BY:  Niranjan Raikar
CREATED ON:  7/17/2019
NOTE:   This sproc will check if dependent job for the given package is completed, 
    if not it will wait for 20 mins and recheck until dependent job is succeedeed

@USAGE = 1 Checks both scenarios for current running job and completed job_id
@USAGE = 2 Checks only for current running job
@USAGE = 3 Checks only for completed job

EX:  EXEC udsp_Dependent_Job_Status_Check 'package name','dependent job name', 1
EX:  EXEC udsp_Dependent_Job_Status_Check 'package name','dependent job name', 2
EX:  EXEC udsp_Dependent_Job_Status_Check 'package name','dependent job name', 3
*/
 DECLARE @STILL_RUNNING VARCHAR(100),
@RUN_COMPLETED VARCHAR(100), 
@CONDITION INT,
@EmailProfile VARCHAR(20) = 'ProfileName',
@EmailId VARCHAR(100) = 'testemail@domain.com'

 select @STILL_RUNNING = NAME
 from msdb.dbo.sysjobs_view job  
 inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id 
 where activity.run_Requested_date is not null  
 and activity.stop_execution_date is null  
 and job.name = @DEP_JOB_NAME

 select @RUN_COMPLETED = NAME
 from msdb.dbo.sysjobs_view job  
 inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id 
 where cast(activity.stop_execution_date as date)=cast(getdate() as date)  
 and job.name = @DEP_JOB_NAME

 SET @CONDITION = CASE WHEN @USAGE = 1 THEN CASE WHEN @STILL_RUNNING IS NOT NULL OR @RUN_COMPLETED IS NULL THEN 1 ELSE 0 END 
        WHEN @USAGE = 2 THEN CASE WHEN @STILL_RUNNING IS NOT NULL THEN 1 ELSE 0 END
        WHEN @USAGE = 3 THEN CASE WHEN @RUN_COMPLETED IS NULL THEN 1 ELSE 0 END
      END

 WHILE @CONDITION = 1
 BEGIN
  PRINT 'WAITING FOR ' + @DEP_JOB_NAME + ' TO COMPLETE' +CHAR(10)
  DECLARE @BDY NVARCHAR(MAX) = 'Waiting for '+ @DEP_JOB_NAME +' job to complete' + CHAR(10) + CHAR(10) + 'Waiting for 20 mins to recheck job ' + @DEP_JOB_NAME +' status'
  DECLARE @SUB NVARCHAR(MAX) =  SUBSTRING(@@SERVERNAME,1,CHARINDEX('\',@@SERVERNAME)-1) + ' : ' + @PKG_NAME+' is waiting for Job '+ @DEP_JOB_NAME +' to complete'
  
  EXEC msdb.dbo.sp_send_dbmail 
  @profile_name=@EmailProfile,
  @recipients=@EmailId,
  @subject=@SUB,
  @body =  @BDY;

  WAITFOR DELAY '00:20:00'

  select @STILL_RUNNING = NAME
  from msdb.dbo.sysjobs_view J  
  inner join msdb.dbo.sysjobactivity A on J.job_id = A.job_id 
  where A.run_Requested_date is not null  
  and A.stop_execution_date is null  
  and J.name = @DEP_JOB_NAME

  select @RUN_COMPLETED = NAME
  from msdb.dbo.sysjobs_view J  
  inner join msdb.dbo.sysjobactivity A on J.job_id = A.job_id 
  where cast(A.stop_execution_date as date)=cast(getdate() as date)  
  and J.name = @DEP_JOB_NAME
  
  SET @CONDITION = CASE WHEN @USAGE = 1 THEN CASE WHEN @STILL_RUNNING IS NOT NULL OR @RUN_COMPLETED IS NULL THEN 1 ELSE 0 END 
        WHEN @USAGE = 2 THEN CASE WHEN @STILL_RUNNING IS NOT NULL THEN 1 ELSE 0 END
        WHEN @USAGE = 3 THEN CASE WHEN @RUN_COMPLETED IS NULL THEN 1 ELSE 0 END
       END
 END

GO

Prerequisites for using this approach

1. Microsoft SQL Server on premise database installation(Azure SQL Database doesn't comes with msdb system db)
2. Email Profile created in MS SQL server  Database Mail Profile