Auto archiving competing for SQL table locks

[Nathan.Jones91]

Hi all, we have been having a problem which has become more frequent recently, where the auto archiving process built in to Blue Prism, is competing for table lock with our regular SQL maintenance job. This is impacting several schedules as they are not starting while the BPASession/BPASessionLog tables are locked. The maintenance job runs out of hours at 23:00, and when it clashes with archiving, can lock the tables for up to 2 hours.

The session log table in particular is just shy of 40GB, and we keep 45 day's worth of logs for reporting purposes. 

Are there any ways to prevent session log archiving from clashing with the maintenance task?

[harish.mogulluri]

Hi @Nathan.Jones91 
Not sure whether  below KB articles will resolve or give an idea to resolve the issue but if you have not seen this KB article its worth to check this KB articles


https://support.blueprism.com/en/support/solutions/articles/7000076966-how-do-i-find-and-resolve-issues-when-blue-prism-enterprise-reports-a-sql-server-transaction-deadlock

https://support.blueprism.com/en/support/solutions/articles/7000077243-how-can-i-check-for-sql-server-locks-when-the-archiving-process-fails-

It seems there is an option to release Archival Lock- Check below article for that

https://support.blueprism.com/en/support/solutions/articles/7000077237-bpe-error-the-machine-is-already-performing-an-archiving-operation-during-archiving

[david.l.morris]

I think the simplest way to solve your issue is to simply not run the two at the same time. You mentioned "auto archiving" so I suppose that would be difficult to do. One way to make this happen is to stop using the auto archiving and instead use a PowerShell script that will call the Blue Prism CLI (AutomateC.exe) to trigger archiving. Then you could schedule your script to run at a different time compared to the maintenance job. For example, the archiving script could just run in the middle of the day while the maintenance job runs at 23:00.