We're experiencing almost everyday database index corruption on Windows Server 2003. I wonder if it can be somehow connected with caching or other server settings.
We are running an old application which uses DBF/CDX tables. Everything was fine for ages, but 6 months after we've installed Advantage Database Server (which allows access to some tables to our website) we started to get index corruption problems. And we don't know whom to blame. ADS returns an error: Error 7017: Corrupt .ADI, .CDX, or .IDX index. Table name: RBOOKM
We've tried to exclude all possible causes of this corruption. Now all users work in terminal mode - so no network problems can cause that, OpLocks also can't be a reason. We changed hardware, network cards, switches, reinstalled Server and even moved to new dedicated server. The only thing we can't exclude is ADS - because it should be working.
Is that possible that local read/write caching that causes that problem? E.g. one user or process uses cached data, later another user/process changes it, and later the first user changes it again without knowing about the first change. Is it possible theoretically?
Is it possible that this problem is caused by importer file server or caching settings? Is it possible that normal users use non-cached data and ADS is using cached data? Or vice versa? Is it possible that each terminal user has its own cache? Or maybe the problem is about RAID caching somehow interfering with Windows Server caching? Or maybe there are some special settings for Windows Server for working with DBF tables that are being written simultaneously by several terminal users? Maybe there is a way to turn off caching for some certain files to check it?
Sometimes we get index crash twice a day, sometimes everything is fine for 5 days in a row. But usually it crashes on daily basis. Today only one user was working in the evening with the database (usually there are 30-50 users are working simultaneously on working hours). So it's almost zero load on server. Synchronization with website is performed every 5 minutes during work hours and every 15 minutes in the evening and on weekend.
We've done file access auditing and it shows that during website synchronizations ADS server opens the table and index files for ReadEA and WriteEA though it performs only SELECT queries. ADS does UPDATE/INSERT queries but less frequently - not during regular synchronizations, but only when an order is placed by website visitor).
Please help me. We are struggling with this problem for almost a year and still can't find any pattern or any clue about this problem.
Here is my previous question about this issue on DBA: http://dba.stackexchange.com/questions/8646/foxpro-dbf-index-corruption