We run a change log report from ACS (Our Church Management System) that shows us any one that has made a change to a specific record and what that change was. This report is an integral part of our quality control and gets used often. We have been experiencing a growing problem where this report takes forever to run. After talking with ACS we found out that we need to clear the log from time to time so that it is not so huge. We did that and the reports ran fine, for a while. Now the problem is back and I just don't understand why.
The ACS server is a Dell 1850 with dual Xeon 2.8 Ghz, 1 GB Ram, SCSI 360. The processor barley moves and the RAM has at least 512 mb free at all times. The other thing that I don't understand is that the hard drive is doesn't have that many reads going on during this report. The GB NIC is only being used at 2%.
The Terminal Server that our staff uses for ACS also doesn't have much load. So where is the bottle neck? I'm open to ideas!