Database Corruption: Why it happens and how to deal with it

Last updated on August 7, 2002

Introduction

The Microsoft Access JET database engine, used by TimeTiger to connect to Microsoft Access databases, suffers from an unfortunate characteristic: occassional "database corruption" messages. This document discusses what this message means, why it happens, how to deal with it, and how to prevent it in the future.

Database Corruption

If you have received a "Your database appears to be corrupt" message in TimeTiger, you are not alone. Many of our users who run Microsoft Access databases under TimeTiger version 1.7 and earlier will occassionally receive this message. We believe that with version 1.8 we have successfully worked around the problem and instances of database corruption are expected to become much more rare. If you are using an older version of TimeTiger and have not yet upgraded, you can rest assured that the warnings you receive rarely indicate that any actual damage has occurred to your data. Typically, you are being told only that a connection between the TimeTiger application and the TimeTiger database was unexpectedly and temporarily severed. When the Microsoft Access database engine detects that the connection was lost, it sets a special "possible corruption" flag in the database that causes the message to appear. Actual data in the database is almost never affected.

From our research, we have found that this "database corruption" can occur fairly frequently on some networks and almost never on others, and has very little to do with how many users are connected or how much load is placed on the database. Network reliability appears to be the determining factor. Procedures on diagnosing and improving the reliability of your network are beyond the scope of this document, but an unreliable network can cause other problems outside of TimeTiger and may be worth investigating if you are able.

Repairing your Database

When TimeTiger detects that your database has been marked as "corrupt," you will be given the option of attempting to repair it. Make sure that all users are out of TimeTiger before attempting to repair the database from within TimeTiger. Do not be alarmed if the repair operation does not succeed: TimeTiger uses a very simple repair algorithm that is not always able to un-mark the "corrupt" flag in the database.

If you have attempted to repair the database from within TimeTiger and were unsuccessful, you now have two options. If you own a copy of Microsoft Access 2000, try opening the database from Microsoft Access 2000 and attempting to repair it from Access. This almost always works. Note that you must use Access 2000: Access 97 and Access 2002 are not to be used.

If you do not own a copy of Microsoft Access 2000, or if you are unsuccessful in repairing the database using Access 2000, simply e-mail your TimeTiger database to support@indigo1.com. We will correct your database and get it right back to you. Please compress your database using a program such as WinZip before attempting to e-mail it.

Preventing Corruption in the Future

Here are some tips for helping to prevent database corruption:

• Upgrade to TimeTiger version 1.8. This is a free upgrade, and is expected to solve most or all database corruption problems.
• Compact your database regularly. We recommend compacting at least one per month for installations of 10 or fewer users and once every two weeks for larger installations. Compacting will help keep your database small and fast, and will reduce the chances for database corruption and other database issues. You can compact your database from TimeTiger Explorer, Tools, Options, Compact TimeTiger Database.
• Consider using "Safe Mode" for Microsoft Access connections. With TimeTiger 1.7 build 45, we have introduced a special mode (which you can turn on from TimeTiger Explorer, Tools, Options, Advanced) that is designed to help reduce or eliminate database corruption on problematic networks. This special mode establishes connections to the database only when they are needed, and releases them immediately after TimeTiger is finished with them. If you experience frequent database corruptions, upgrade all of your machines to the most recent build of TimeTiger and turn on Safe Mode. You may notice a slight performance penalty within TimeTiger, but the frequency of database corruptions you experience should drop dramatically. Download the latest build of TimeTiger and be sure to uninstall your current TimeTiger before installing this one.
• Switching to a Microsoft SQL Server database will eliminate the database corruption problem entirely as Microsoft SQL Server does not suffer from the same connection-dropping behaviour as the Microsoft Access JET engine.

For more information about this document or if you have any questions, please contact support@indigo1.com.