TechTips: Corrupted tables and indexes, their causes and solutions

TechTips: Corrupted tables and indexes, their causes and solutions

Post by Sundial Servic » Tue, 22 Jul 1997 04:00:00



A file-server based database such as MS-Access or Paradox might be the
ultimate in simplicity or convenience, but the one drawback of this topology
is that it is dependent on the good behavior of every computer on the network.
Let the record show that we have Paradox clients with forty or fifty users
banging away on a database all day every day, so it works just fine, but one
drawback you will see now and then is "database is corrupt," or "index out of
date," or something of that sort.  Let's look at that.

OHMYGODWHATDOESITMEANI'MDOOMEDMYCUSTOMERWILLKILLME?!?!?!
Don't jump off that bridge when you get there, pal... your data is probably
quite safe.  Messages like this one are designed to appear when there is any
reason to doubt that all of the table-structures, indexes and so-forth might
*not* be 100% current and correct.  Microsoft Access is particularly
conservative:  it is designed so that if every single user of the file does
not properly close it, the database will be marked as "corrupt" specifically
to force you to run a repair.

WHAT COULD HAVE HAPPENED?
The most common thing that can happen is basically "index out of date."  Each
table can have a number of associated index-structures that must be kept
current with the information at all times.  Index tables and data tables
contain counters, maintained by the system, which indicate if they are
in-sync.  When they are not, the table-repair process will rebuild the index.  
The data itself is not affected.

The next most likely possibility is an error such as a truncated table, where
something went wrong while a program was in the process of adding a record,
deleting one, or moving something around.  When table-repair processes find
this kind of error, they read the table files sequentially looking for
anything that might be a good data block -- kinda like Norton Utilities.{tm}  
But this condition is more rare because it presupposes that the failure
occurred during an actual file-write.  (There was an error in early Delphi
programs, however, which prevented updates from being written to disk in a
timely manner, causing a rash of such problems.)

The least likely occurrence is that the file-header or some other part of the
file got overwritten.  Most of the time the database software simply isn't
gonna do that.  But another errant program might.

HOW CAN IT BE FIXED?
Generally, the table repair processes provided by the vendor are the only best
reliable solution.  We've had some successes in Paradox where we found out
which index was bad, deleted the index files from the hard-disk, and recreated
the index.  But you can't do that in Microsoft Access, where everything lives
in one huge file.

In Microsoft Access, it is critical to run "repair database" first, then
"compact database" second.  The "compact" step might be optional but
definitely remember to do "repair" before you do "compact."

OTHER THOUGHTS?
Backups.  All of your database files should be backed up -at least- once a
day.  Very heavily used databases should be backed up at lunchtime, as well.  
It must be a staff-level responsibility for someone to ensure that the backups
did run and that no problems occurred.  There are plenty of great packages out
there which can do this, so there is NO EXCUSE not to do it.

As an aside, we strongly recommend using higher-quality media such as
magneto-optical disks or DAT tapes, instead of the cheaper, slower, and much
less reliable tapes that are more readily available.

And finally, make appropriate plans for the inevitable corruption/index
problems that might occur.  Look not only at what the database software vendor
sells but third-party tools as well.  Your needs will vary depending upon the
knowledge-level of the staff at your particular site, but arrange things so
that whenever a database goes down someplace they don't have to always call
you.  DO NOT CONSIDER THESE TOOLS TO BE A SUBSTITUTE FOR A SAFE, RELIABLE,
WE-KNOW-WE-CAN-REALLY-RESTORE-FROM-IT-WE'VE-PRACTICED, CURRENT BACKUP KEPT IN
A SAFE-DEPOSIT BOX DOWN THE STREET OR IN A FIREBOX.

SO, IS THE WORLD GONNA END?
No, sleep tight.  Most of the time database corruption doesn't happen, which
makes it feel rather like a gunshot in the head when it does.  However, if you
are experiencing a rash of corruption-problems, perhaps suddenly, look for a
reasonable cause.

Your software, of course, should be current.  Your hard disk must be
shipshape.  You have to configure the software correctly according to the
vendor's instructions.  Look for specific vendor frequently-asked-question
files at their respective web-sites, or in www.dejanews.com.

-----
? Mike Robinson; Sundial Services, Scottsdale AZ
 (602) 946-8259; fax (602) 874-2068

 "Your computer can do what you want!"{tm}  since 1992.

Last time I checked, "Norton Utilities" was a trademark of Symantec Corp.  You
know about the other trademarks.