We are running a pretty popular in Russia and other CIS countries though a pretty old accounting system called BEST. It's all built on FoxPro (not Visual Foxpro) DBF/CDX tables. It's open and it doesn't have API for interacting with other software. So we have to use direct access to tables and data.
We use Sybase Advantage Database Server Internet Connector (ADS) to connect our online store to BEST databases and to receive fresh info and write orders back. BEST users work on Windows Server 2003 R2 SP2 in Terminal mode - so all the executables are run and all tables/index files are opened locally on Windows Server, but not over the network.
Last 2 years we have problems with frequent database file locking. First we thought we have Foxpro index file corruption, because all errors from ADS are index-file related. But later we found that those files (both DBF and CDX) are ok and after reboot they open normally. Usually the problem happens with 3 tables: RBOOK (orders), RBOOKM (orders contents) and MKART (storage info about goods - quantities, reserves and so on). Some of the users continue to use those files, while others can't open them. I can copy the files with Windows Explorer, I can open the table with FIWIN dbf-viewer, but I can't with ADS or BEST.
It's hard to troubleshoot as the problem is not showing itself immediately. No system event log values somehow connected with it. Unfortunately, BEST doesn't have a normal log file (just a file where it writes last error, which usually says about error opening index file). The problem is frequent - it happens almost every day, but it's every time it happens at different time. We tried to find any regularity, but we failed. There are periods when there are no problems for 10 consequtive days, and then it returns to 'daily basis'. Rebooting is the only way in our situation. Without rebooting (with just closing all apps and reindexing) the problem repeats very soon.
We can't use SysInternals Handles.exe utility as it always says that there is not enough resources to run. We used Windows Task manager to see number of handles that are open by applications - but the biggest numbers are for Outlook, acronis MMS service and svchost. There are not processes connected with our tables that have > 1K handles. We tried to switch off everything possible (AV software, backups, unnecessary utils), we limited access to whole departments, we logged access to problematic files, we tried changing lots of Windows registry keys that are somehow connected to files, locking (oplocks), network, memory and handles limits and so on. But no luck. We clean reinstalled the server, but this didn't help either. We replaced memory, hard disks, network switches. Later we replaced the whole server. We changed power supply. We recreated user profiles. We changed NICs on user machines and network cables and later put everybody on terminal access. We tried to monitor resources and did other things, but everything looks fine.
Here is a screenshot of a Unlocker utility which is shot during RBOOKM index file access problem. http://cl.ly/image/292d17111A3B What seems strange to me that many processes have identical handle file ID for the file, while others have unique handle.
One more thing that is probably connected with this problem - we noticed that in several cases CPU was more than 60% utilized and system was working rahter
Do you have any ideas? I'll be greately thankful to you.