Every now and then I have Sage freeze and lock everyone from doing anything. It commonly happens in the payment batch list during heavy usage around month end, but it can happen other times as well. The system during the working day is under heavy usage via an integration pushing AR and AP batches in, around a batch every few seconds or so. When the issue first started happening, I created a SQL maintenance plan to reindex and rebuild the SQL statistics and this was scheduled to run twice a week. This maintenance plan does actually resolve the issue insofar as Sage will work again. It is now scheduled to run daily out of hours and that helps, but once or twice a month the problem occurs and the maintenance plan needs to be manually run between scheduled runs and again, the problem goes away. The problem is we have never gotten to the bottom of what precisely is causing the issue. I can't run SQL profiler on it and wait until the issue happens because the load on the system is then too great. When it does happen, it needs to be fixed ASAP. I have logged cases with Sage in the past but until the issue happens, there is nothing they can do, and when it happens, I need them on the machine immediately which is never going to happen. The SQL database is about 45gb and the logs are around 12gb. There is an automated full back up every day plus hourly incremental backups. Rebuilding and reorganising the indexes only takes less than a minute. Rebuilding the statistics takes about 25 minutes. There isn't anything in the event log that stands out around any of the times where the schedule was manually run.
Customer has 2018 PU5. Yes it's old. Not my call to upgrade. PU6 and 7 don't appear to have anything that may fix the issue.
SQL is 2016 SP2 CU7.
Just hoping to get some potential insight into what might be causing this and maybe some insight into how to resolve it.