Database Troubleshooting Flow Chart

See also: Introduction Troubleshooting flow chart + worksheet How to troubleshoot damaged data Database myths Causes of data damage How to repair How to export/import Troubleshooting FAQs Tips for preventing damaged data Inside the database Database size Windows tips

Some database damage is obvious. Problems that cause bad header, bad pointer, or bad index error messages usually appear first in the Quick Check (an automatic self-diagnostic function) shortly after they occur. That’s the best time to troubleshoot the problem and find it’s cause -- while the circumstances around the event are still fresh in everyone’s mind. But some data damage can be less apparent, such as when some jobs don’t appear on a job list or a G/L account can’t be found. Or, the database might have bad data, but not actually be physically damaged. In these situations (for example, when some account totals don’t add up) determining just what’s wrong is vital to solving the problem. Because if the database isn’t really damaged, repairing it won’t solve the problem -- and just waste time. Since there are several degrees of data damage, the steps you’ll take to fix the damage depends on just what’s wrong. The possible fixes are listed in the flow chart (see right) and in the tables (see below). If caught early, almost any kind of data corruption can be repaired successfully and data won’t be lost. But if the source of the problem is NOT found and resolved, the errors will keep occurring. So it’s important that you make the right choices at the first sign of trouble -- not after weeks or months of errors. When you call us for help with a damaged database, our first and best suggestion will be to restore a backup. If you don’t have a backup (or the backup is old) we can guide you through different steps to attempt to fix your database. A built-in Database Utilities function provides tools to check and repair data corruption. WHAT TO DO WITH DAMAGED DATA: 1. REINDEX Try this first: reindexing Any kind of power failure, system crash, network communications error, or other hardware problem can cause minor corruption in the C&P database. This corruption causes records, such as jobs, clients, or G/L accounts, to behave erratically -- but usually not enough to keep people from working. Users usually notice it when certain jobs, time entries, etc. don’t appear on reports. Using Database Utilities to reindex the damaged data file typically solves the problem with a minimum of downtime. 2 - QUICK CHECK If damage is found, run the Quick Check Clients & Profits performs an instant “quick check” every time a manager-level user opens the database. It checks for bad records, pointers, and other damage. If damage is found, the Quick Check displays a log describing the problems and prompts the user to repair it on the spot. Since quick checks occur several times a day, damaged data will be found nearly as soon as it happens &emdash; which makes it quick and easy to fix. Repairing corruption from the Quick Check is almost always successful. 3 - REPAIR If the Quick Check can’t fix it, try repairing it with Database Utilities Databases that are more severely damaged can’t be fixed by the Quick Check. The repair tools in Database Utilities can fix what the Quick Check can’t, but take much longer. It checks every record in a data file for integrity, then fixes indexes and pointers. If some data is irreparably damaged, it will be purged. See page 8 for step-by-step instructions. 4 - EXPORT/IMPORT If the database can’t be repaired, there’s still a chance the data can be saved by exporting it into a new database When a database can’t be repaired, its physical structure is too corrupted for Database Utilities to make sense of the data. If so, the Export/Import utility is the last option. It saves your raw client, job, cost, and accounting data as text files then imports them into a brand-new database. The export/import process is slow and will purge any unusable records, which means you’re likely to lose data. See page 10 for step-by-step instructions. 5 - RESTORE BACKUP If reindexing, repairing, and exporting/importing fails, the only option is to restore a backup When a database can’t be repaired or exported the only choice is to restore a backup. Restoring a previous day’s backup is the quickest and most reliable way to get the shop working again. Restoring can take less than an hour, compared to days of waiting for a report or export/import to finish. The drawback is that you’ll lose any data added or changed since the backup was made -- that’s why it’s critical to make daily backups. IS THE DATA DAMAGED OR NOT? BAD DATA -- no repair needed DAMAGED DATA -- needs repair Can't print reports Entering a job number doesn't find anything G/L is out of balance Jobs don't appear on job reports One-sided journal entries in G/L Data can be seen in windows, but not on reports Job totals don't equal job costs Records contain gibberish Billing amounts on A/R invoices are wrong Records can be found, but can't be edited Agings don't equal Balance Sheet Changes made to a record aren't be saved. Wrong hourly rates on time entries   TROUBLESHOOTING WORKSHEET Typical Problem: V/R* Reindex Repair Export Restore G/L is out of balance X         Vendor or client aging doesn't equal the totals on the Balance Sheet X         Client aging doesn't equal the A/R total on the Balance Sheet X         Reports don't print data than you know exists X         Entering a job number doesn't find the job, and you know its there   X       Even after verifying, aging reports don't equal totals on the Balance Sheet**   X X     Out of Balance Checker shows JEs that can't otherwise be found   X X     Quick Check fixed the data, but it still doesn't work     X     Tried to repair the database, but it seems to be frozen on one data file       X   Data was repaired, but records were lost         X We exported/imported the data, but records were lost         X No one can open the database         X The database can be opened but contains no data (everything is missing)         X The database is damaged, but there's no time to export/import         X Export/import has been running, but seems to be stuck         X G/L is out of balance         X * Verify/Recover isn’t used to repair damaged data. Instead, it has functions to verify the totals on jobs, clients, vendors, G/L accounts, POs, and more. If your database isn’t damaged, verifying the data will correct any posting problems or inaccurate account balances caused by system crashes, freezes, etc. If your database is damaged, the verify/recover utility probably can’t correct any balances until the database is repaired. ** This could also mean bad data. Before repairing, use the Auditor to find user data-entry and posting errors. Next Page: How to Troubleshoot Damaged Data