Prevent the RiskFabric Intraday Processing job from starting when the nightly RiskFabric Processing job is running

Products

• Information Centric Analytics

Issue/Introduction

Under certain circumstances, the nightly RiskFabric Processing job may run longer than its historical average. When this happens in an environment in which the RiskFabric Intraday Processing job is enabled, there is a risk that the intraday job will start before the nightly job has completed. Because both jobs execute many of the same tasks, access the same database objects, and utilize the same system resources, you should take care to prevent this condition by adding a check to the intraday job that will prevent it from running concurrently with the nightly job.

Environment

Release : 6.x

Component : RiskFabric Processing, RiskFabric Intraday Processing

Resolution

1. Open SQL Server Management Studio (SSMS)
2. Connect to the Database Engine hosting the RiskFabric relational database
3. In Object Explorer, navigate to SQL Server Agent > Jobs
4. Right click the RiskFabric Intraday Processing job and select Properties
   The Job Properties - RiskFabric Intraday Processing window opens
5. Select the Steps page
6. Select Step 1, then click the Insert button
   The New Job Step window opens
7.  Enter the following details:
   • Step Name: 1:RiskFabric Processing job check
   • Type:            Transact-SQL script (T-SQL)
   • Run as:
   • 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)

8. Select the Advanced page
9. Ensure that On failure action is set to Quit the job reporting failure
10. Click the OK button to commit changes
11. Ensure the newly added step is the first step, change the Start step to 1:RiskFabric Processing job check, then click the OK button to commit changes.