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.
ICA (all versions)
Open SQL Server Management Studio
Connect to the Database Engine that hosts ICA
Expand SQL Server Agent, then jobs
Right click on the RiskFabric intraday Processing job and select Properties
Select Steps, Click Step 1, then Insert
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)
Ensure that the On failure action is set to “Quit the job reporting failure”
Click OK to commit the changes
Ensure the newly added step is the first step, Change the Start step to 1, then click OK to commit the changes.
Subscribing will provide email updates when this Article is updated. Login is required.