Orchestrator Queues Page not updating InProgress/Remaining stats

At times Orchestrator’s Queues page doesn’t update the stats and number of items In Progress and Remaining doesn’t update. Bots are actually processing transaction items but stats are not getting updated.

image

If you look at the windows event log in Orchestrator server you will see error message with SQL’s datediff function failing.

“The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart. System.Data.SqlClient.SqlException (0x80131904): The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.”

This happens if you have a transaction item that was processed long ago and for whatever reason you put that transaction item in InProgress status and then in Success/Failed status. Under the hood UiPath’s database keeps a track of when transaction item has Started Processing and Ended Processing in QueueItems table. The query used by UiPath to calculate Stat for transaction processing average time use datediff that will fail for a long time difference between StartProcessing and EndProcessing dates. They should have used DateDiff_BIG function from SQL instead of datediff in UiPath application but that is a fix UiPath needs to make.

How can you solve this issue in the mean time?

Find offending records from QueueItems table and make a comma separated list of Ids from this query.

select *
,DATEDIFF_BIG(MILLISECOND,QueueItems.StartProcessing, QueueItems.EndProcessing)
from QueueItems (nolock)
where Status IN (3)
and DATEDIFF_BIG(MILLISECOND,QueueItems.StartProcessing, QueueItems.EndProcessing) > 2147483647
order by DATEDIFF_BIG(MILLISECOND,QueueItems.StartProcessing, QueueItems.EndProcessing)

Now run a select query on UiPath database.  Ids needs to be from the first query.

Select * from QueueItems where Id in (1,
2,
3)

Now Run an Update Query on this table to make all these QueueItem’s EndProcessing time little bit after StartProcessing time. You can find the average time your transaction item takes to process so just update the EndProcessing time by that much. For example if transaction is taking an average of 1 second to process you can update these items by 1 second. Be careful with what you update. Run select query before running update to make sure you are not accidently updating wrong records and always remember to take backup before updating records.

Update QueueItems Set EndProcessing = DATEADD(ss,1,StartProcessing) where Id in (
1,
2,
3 )

That will resolve the issue.

2 Likes