We are facing a problem where CPU usage on SAP Business One server spikes to 100% and during this period all SBO Clients for all users stop responding. It takes 3-5 seconds and then clients start responding. When it happens then Data File I/O in SQL Server shows that tempdb is being read/written by about 20MB/Sec. It is happening after every 5,6 minutes. Our tempdb is about 2GB in size. We are in retail environment where sale transactions from many POSs are coming to SBO Server continuously and our primary MDF is around 80GB, its LDF around 20GB and SBO Common MDF is around 12 GB and its log file is reaching 50GB. What we need to do to fix this issue?
One query used in an alert was failing for some reason. And it was being called every minute. Not sure why because we had set it to run only one time each day. We ran some sql queries to know what was running inside sql when CPU usage went to 100%. We found that alert query and disabled it and CPU usage immediately became normal and SBO clients also became normal and problem fixed.