Test for index corruption

[SteveDingle]

Heya All,

VFP 7 database - but have access to VFP 9.

Client is having on/off problem with their indexes. THey are running quite a few external apps against the database, SQL Server queries/.Net DLL's etc.. and the assumption is that one of them is causing the problem.

In order to help narrow down the problem I am setting up a test database (complete copy 10+ gig) that I will run the processes against, one at a time, hoping this will help my find the culprit.

To do that I would need a sure fire test against a given table/index to see if that index file (CDX) is actually corrupt.

Anyone know of a 100% guaranteed index tester?

Toodles,
Steve Dingle

http://www.dsbusinesssolutions.co.uk

D&S Business Solutions Ltd

 

[Olaf Doschke]

Anyone know of a 100% guaranteed index tester?

No, there are much too many different ways, an index might b ecorrupt. Sometimes even triggering a C5 error when just opening the table, sometimes just not finding the right record corresponding to a seeked value.

I'd loop through the indexes from 1 to TAGCOUNT(), determine their expressions with KEY(), then Select cKeyExpression From Table into cursor, then Seek each value. If the index is okay, it should find all the values.
If it finds all values there still might be a curruption that only triggers an error if you seek something not in the table.

Maybe the easiest way - although the database size is quite big already - is to reindex the tables regularly. That shouldn't be done using reindex, but with delete tag all and a script containing all the needed index on or alter table (for primary keys).

In my experience index corruptions are more likely if the btree of the index get's older. The most probable reason for corruption of fresh indexes can then only be LAN or HDD failures.

If you don't want to fiddle with that rebuild of indexes, Stonefield Database Toolkit does that with it's repair routines, that can also be used preventional.

Bye, Olaf.

 

[SteveDingle]

Heya Olaf,

Thanks for the quick response. I had thought about the search idea but with 10's of millions of records and some tables being 1+ gig, well I was hoping for more efficient method

As for reindexing reqularly, the client doesn't seem to be to keen on that. It takes over 1 hour for the whole database and in theory it would have to be done each night and they already have processes running during the evening. FWIW, I do have SDT and thats whats being used.

And at the end of the day I'd really like to know what's causing it.

Thanks for your thoughts

Toodles,
Steve Dingle

http://www.dsbusinesssolutions.co.uk

D&S Business Solutions Ltd

 

[JRB-Bldr]

Running a Reindex routine on some server constantly would not work since it would require EXCLUSIVE access to the data tables and prevent users from running the other utilities of the application.

But as to "And at the end of the day I'd really like to know what's causing it."[/U], you might consider utilizing an Error Handler to trap the errors when they occur.

I made some modifications to the ErrHand routine in faq184-4492 (maybe also look at faq182-4156). Those modifications now not only display an error message to the user, but it saves the data to a data table for after-incident review.

By trapping the UserName, the Workstation, the Date & Time of the Error, the Program with Error, the Error Message, and the Memvar, I find that many problems can be localized, identified, and resolved.

You could further expand this captured data in a variety of ways to help determine other parameters such as Workstation resources available, Network drive access capability, other Windows tasks running at the time of the problem, etc.

Good Luck,
JRB-Bldr

 

[SteveDingle]

Heya JRB-Bldr,

Thank for your thoughts.

FWIW, I don't think Mike meant to run the reindexing constantly, just the test for finding records. Which if nothing else come around I suppose I will try to implement atleast during the testing process.

As for error handling, my framework does have classes and routines to deal with and log error whereever possible. The problem is we're not seeing any errors during data entry (which is when index corruption would occur) but only when a process is run that tries to access the data AFTER the corruption has occured - either it's not finding records we know exist or in worst case a C5 error.

I also beleive the corruption is coming from processes being run against the data which is outside my applications.. though I can't be sure.. and with those NON-vfp apps I don't have any control over.

Would be nice if there was an "index cocurrupted event" or something like that so I can trap for when it happens but I can't think of a way to do that so the idea of running a process, test, etc..

Toodles,
Steve Dingle

http://www.dsbusinesssolutions.co.uk

D&S Business Solutions Ltd

 

[Dan Freeman]

Steve,

If you talk to Doug, he'll tell you exactly why there's no "validate indexes" support in SDT. It can't be 100% effective and takes less time to just recreate them.

I take it you've done the basic gruntwork of verifying power supplies on the workstations and network stability?

Index corruption is a sticky wicket. I have clients with hundreds of users doing data entry who NEVER see index corruption, and other clients who can cause it at will. <sigh>

Dan

 

[SteveDingle]

Heya All,

Mike: Thanks for the feedback and links, shoulda checked out the wiki myself.

DFAPAM: How the heck are ya? I remembered there was always a problem with the index "testing" but hadn't raised the questionin donkey's years and had a need, thought I would throw it out there [shrug]

BTW, when ya gonna come across the pond so we can do some careening in the UK pubs and you can get a taste of what beer should taste like beer [gd&r from USA beer drinkers], also after only 3 years here I finally got them to extend the serving hours past 11pm ;-)

Toodles,
Steve Dingle

http://www.dsbusinesssolutions.co.uk

D&S Business Solutions Ltd

 

[Dan Freeman]

I think I'm just about recovered from our last careen. ;-)