SQL query for Orchestrator database QueueItems maintenance

Help
1

Hello,
We had a lot of data at QueueItems table from orchestrator database and that was making our performance slow. We went for info from Maintenance Considerations and that database query can filter by creation time, status and/or tenant. It was really useful but we still were having timeout issues. We keep looking into database and we found out there are QueueItems (transactions) related to deleted Queues (Deleted queues can not been recover from orchestrator web interface), so those QueueItems could be deleted from database.
We want to share a query to delete those “deleted” QueueItems:

delete FROM DBO.QueueItems WHERE ID IN (select qi.Id FROM DBO.QueueItems qi inner join QueueDefinitions qd on qi.QueueDefinitionId=qd.Id WHERE qd.IsDeleted=1)

if you have a lot of QueueItems you can delete by slots:
delete FROM DBO.QueueItems WHERE ID IN (select TOP 1000 qi.Id FROM DBO.QueueItems qi inner join QueueDefinitions qd on qi.QueueDefinitionId=qd.Id WHERE qd.IsDeleted=1)

Remember to do a backup before.

We would like to receive some feedback about this query, let us know how can we improve it or if you don’t recommend it’s use.

Regards,
Juan Felipe Carvajal

1 Like
2

Hi,
what about your query? is it fine from perf point of view?

About QueueItemEvents table, have you cleaned it?

BR
Maurizio

3

Hi,

We figured it out that delete from QueueDefinitions where isdeleted = 1 deletes QueueItems and QueueItemEvents.

Is it fine from performance point of view?, I don’t know, it is the way we are doing it right now but we are sure there is a better one. The reason we posted is to get feedback from community and UiPath.

Regards,

1 Like
4

It’s a good approach, We use one but with some changes (Based on Days and per tenant)

1 Like
5

Thanks, man. I used your query to clear out our Azure Database, as it was causing performance issues. Worked like a charm! Deleted all the QueueItems that were deleted from Orchestrator and kept those currently in Orchestrator.

For our purposes, we do not need Azure to be maintaining a record of items that were deleted from Orchestrator, because our robotic processes create those audit trails and logs internally as well as in Salesforce.

Now, I’m just going to apply the same approach for dbo.Logs…