How to add Nightly Processing check to the intraday processing job

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.