Queue Analytics Not Updating

News Knowledge Base
1

Queues page in Orchestrator shows all zeros even after adding items/transactions to queue successfully. Monitoring Page on Orchestrator is not updating.

Issue Overview:

Queues page in Orchestrator shows all zeros even after adding items/transactions to queue successfully or the Monitoring Page on Orchestrator is not updating.

Root Cause:
This typically happens when one of the quartz triggers in the SQL database goes into an ERROR state. These triggers are important as one of the things they are used for is running reports that update Orchestrator.
Check Event Viewer logs on Orchestrator to see what operation failed and caused this error.
SQL logs can also be checked to find the root cause.

A common cause for triggers going into ERROR state is a network outage that prevented the SQL server from communicating with Orchestrator.

Resolution:

  1. Open the SQL database for Orchestrator.
  2. Create a Function to convert to ticks
    • CREATE FUNCTION dbo.ToTicks (@DateTime DATETIME2)
      RETURNS BIGINT
      AS
      BEGIN
      RETURN DATEDIFF_BIG(microsecond, '00010101', @DateTime) * 10 + (DATEPART(NANOSECOND, @DateTime) % 1000) / 100;
      END
  3. Detect stuck system jobs
    • SELECT *
      FROM [quartz].[QRTZ_TRIGGERS] (nolock)
      WHERE NEXT_FIRE_TIME < (
      SELECT dbo.ToTicks(DATEADD(minute, -2, getutcdate()))
      )
  4. If any results run the update below which will set all the internal triggers to run in the next 2 minutes
    • UPDATE [quartz].[QRTZ_TRIGGERS]
      SET NEXT_FIRE_TIME = (
      SELECT dbo.ToTicks(DATEADD(minute, 2, getutcdate()))
      )
      ,TRIGGER_STATE = 'WAITING'
      WHERE [quartz].[QRTZ_TRIGGERS].JOB_GROUP = 'ORCHESTRATOR_INTERNAL'
  5. Detect stuck triggers, if any results enable/disable the trigger in the query from the frontend (any remaining would be from Automation Triggers)
    • SELECT rl.[Name] AS ReleaseName
      ,prsch.[Name] AS TriggerName
      ,prsch.[StartProcessCron]
      ,QT.[TRIGGER_NAME] AS QuartzTrigger
      ,QT.[PREV_FIRE_TIME]
      ,CAST(QT.PREV_FIRE_TIME / 864000000000.0 - 693595.0 AS DATETIME) Previous
      ,QT.NEXT_FIRE_TIME
      ,CAST(NEXT_FIRE_TIME / 864000000000.0 - 693595.0 AS DATETIME) Next
      ,j.[Key] AS JOBKEY
      ,j.[StartTime]
      ,j.[EndTime]
      ,j.[State]
      ,j.[RobotId]
      ,j.[ReleaseId]
      ,j.[Source]
      ,j.[BatchExecutionKey]
      ,j.[Info]
      ,j.[IsDeleted]
      ,j.[CreationTime]
      ,j.[HostMachineName]
      ,r.[Name] AS RobotName
      ,r.[UserName]
      ,r.[ExecutionSettingsData]
      ,r.[Version]
      ,r.[Type]
      ,rl.[Description]
      ,rl.[ProcessKey]
      ,rl.[InputArguments]
      ,rl.[ProcessSettings]
      FROM [dbo].[Jobs](NOLOCK) j
      JOIN [dbo].[Robots](NOLOCK) r ON j.[RobotId] = r.[Id]
      JOIN [dbo].[Releases](NOLOCK) rl ON rl.id = j.ReleaseId
      INNER JOIN [dbo].[ProcessSchedules](NOLOCK) prsch ON j.StartingScheduleId = prsch.id
      JOIN [quartz].[QRTZ_TRIGGERS](NOLOCK) QT ON prsch.ExternalJobKey = QT.JOB_NAME
      WHERE QT.NEXT_FIRE_TIME < (
      SELECT dbo.ToTicks(DATEADD(minute, - 2, getutcdate()))
      )


1 Like