ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

How to add Nightly Processing check to the intraday processing job

book

Article ID: 172119

calendar_today

Updated On:

Products

Information Centric Analytics Data Loss Prevention Core Package

Issue/Introduction

Occasionally, the nightly processing job may run for a long period of time. When this happens, there is a risk that the Intraday job may start while the nightly job is still running. Since there are similar tasks called by each job, care should be taken to prevent Intraday from running if Nightly processing hasn’t finished. This can be accomplished by adding a step to check if the Risk Fabric Nightly Processing job is running to the Risk Fabric Intraday job. It is assumed that the database names and job names are default, if not adjust accordingly.

Environment

ICA (all versions)

Resolution

  1. Open SQL Server Management Studio
  2. Connect to the Database Engine that hosts ICA
  3. Expand SQL Server Agent, then jobs
  4. Right click on the RiskFabric intraday Processing job and select Properties
  5. Select Steps, Click Step 1, then Insert
  6.     On the New Job Step dialog box enter the following details:
            Step Name: Risk Fabric Processing Job Check
            Type: Transatio-SQL
            Run as: (Blank)
            Database: MSDB
            Command:

    DECLARE @sessionID int,@jobName VARCHAR(255) = 'RiskFabric Processing'
    SET @sessionID = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity where job_id = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @jobName))

    IF EXISTS (SELECT sj.name, sja.*
    FROM msdb.dbo.sysjobactivity AS sja INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
    WHERE sja.start_execution_date IS NOT NULL
    AND sja.stop_execution_date IS NULL
    AND sja.session_id = @sessionID
    AND sj.name = @jobName)

    RAISERROR ('Nightly job is still running', 16, 1)


    Example:
  7. Click Advanced
  8. Ensure that the On failure action is set to “Quit the job reporting failure”
  9. Click OK to commit the changes
  10. Ensure the newly added step is the first step, Change the Start step to 1, then click OK to commit the changes.