Deleting old queue items in Bulk

[BarışKarakaş1]

Hello Everyone,
We want to delete some old workqueues in our prod environment. All of them are paused in control room. In order to do that we must first delete the workqueue items (around 400.000 items). What are your recommendations for this? What is the most optimal solution that wouldnt effect our prod environment?
1- First option is to develop a simple flow that basically gets completed items and exception items with maximum item count then to loop through them and delete them. This is what we have been doing for a day now. Problem is our prod blue prism experiences some level of choking and I have seen this message in one of my workqueue items: "Execution timeout expired The timeout period elapsed prior to completion of the operation or the server is not responding" To me this is an indication that we are overloading the Blue Prism server with too much DB interaction requiring work. I have also experienced that running that flow from my personal machine in studio (with prod connection) incurs some exceptions. Especially the execution of get completed/exception items actions are very unstable. Consequently this consumes a fair amount of license too.
2- Other option is to run a SQL script that basically removes the rows from dbo.BPAWorkQueueItem table where queueid value is in (Select id from dbo.BPAWorkQueue where running = 0) Reason we didnt attempt this option is that it is not recomended to meddle with the Blue Prism prod database and honestly we are worried about the potential problems may incur. Maybe this is the fastest and problemless option for this. Has anybody done something like this in the past?

Our Database admin team had a backup of the workqueueitems I mentioned. We dont have a strict deadline, we just want a clean and neat control room without the retired workqueues. 
Any recommendations are appreciated
Thank you for your time,
Baris

[SteveBoggs]

Hi Barıs,

Hopefully others can respond with their own best-practices for maintaining tidy work queues, but I wanted to make sure you were aware of a direct database option for this. We in Support have a set of example "Housekeeping Scripts" you can request that may help here -- one such script is "trim_queues.sql", described below:

"Running this script will trim data in the BPAWorkQueueItem table and truncate the BPAWorkQueueLog table. The BPAtag table is also trimmed, removing any tags no longer being used by work queue items.
When clearing down the BPAWorkQueueItem table, the script uses a threshold of when the item was completed. It removes all BPAWorkQueueItemTag entries for the affected item records. As a result, queue items will no longer be accessible in the UI or a process.
The script will keep any items which were marked as finished a specific number of days ago, or have not been marked as finished yet."

If this sounds like it would meet your requirements, you can request these example scripts by opening a Support ticket with us. More information on the scripts themselves can be found in the KB, "How do I get the database maintenance scripts?".

[harshilcibc]

Hi Baris, 

Thank you for sharing your experience. We moved to BP 7.1 last year and started seeing "Execution timeout expired" a lot. I am curious to know some details to join the dots here:

1. What Blue Prism version you are using where this issue is happening?

2. Do you happen to have queue encryption?

Regards,

Harshil