Q. I tried to open the database, but couldn't. Instead, I just got an error message. Why?

This error indicates either a problem with the file server or that the database has been badly damaged. If you can't open the database, then the file server probably thinks the database is in use by another program -- or is simply unreadable. Make sure everyone quits Clients & Profits, then try opening the database. If it still won't open, restart the server. If the database still won't open, replace it with a backup.

Q. A user just told me that last week she got a ‘bad pointer' error message when saving an A/P invoice. She continued working and since then everything has been fine. Should I be concerned?

Yes. A bad pointer, bad header or bad index error should not be ignored. If errors like this are left unchecked and people keep using the damaged data, the corruption can be serious -- and the database may become unusable. Data errors should always be resolved as soon as possible.

Q. Our damaged database was recently repaired. It seemed to be working fine for a while, but now we're having problems again. Why?

Without troubleshooting the cause of the damage, repairing the database solves only half of the problem. Whatever damaged the database will continue to affect it indefinitely -- it'll be an endless cycle of data problems and repairs. See the Clients & Profits Database Guide's "Tips for Preventing Data Damage" for suggestions.

Q. We're trying to repair our damaged database using Database Utilities, but it's been running for a very long time. Is this normal?

Possibly not. The repair tool in Database Utilities typically takes a long time to run (about 15 minutes per megabyte depending upon the speed of your system). But if the repair function is stuck on a specific data file for many hours, it's probably frozen. This can happen if the database is being repaired on a fragmented or otherwise problematic hard drive. If so, check the computer's hard drive for bad blocks, fragmentation, etc. -- or try a different computer -- and run the repair again.

Q. We're constantly verifying/recovering jobs, tasks, costs, etc. Why does it only seems to correct problems temporarily?

That's because the true cause of the data problems isn't being found and fixed. Unless your system crashes while Clients & Profits is saving or posting, you should never have to verify/recover anything. But if your database is damaged, saving and posting won't work correctly every time -- that's the clue that there's real trouble somewhere in the system.

Q. How can I really know what damaged our database?

Be sure to talk to the users who were first affected by a particular error -- find out exactly what they were doing just before the error occurred (i.e., posting, saving, entering data, printing, etc.). Then look for any changes in the hardware or system software configuration, since something may have been installed or set up incorrectly. Use these facts to work backwards from the time the error happened, then look for patterns. You may not find the answer right away, but you'll eventually build a history that will tell the story.

Q. Our computer consultant says Clients & Profits must have damaged the database, because he says he's never had problem with files from other programs.

If your consultant does not have experience supporting multi-user databases, then they're not going to understand the nature of RDMS' like Clients & Profits. Because it is multi-user, Clients & Profits is distinctly different than commonly-used programs like Excel, Quark, Word, or Photoshop. There probably aren't many other programs where the same document is used day after day for years by many users at the same time. In most productivity applications, documents are created, saved, then opened again infrequently -- if ever. If these documents were to be damaged by a server's hard drive, you probably won't even know it. Also, the size of the Clients & Profits database is much larger than the usual Excel or Word file. The sheer size of the database means it's a bigger target for hard disk problems and other errors. That's why you're more likely to see problems with programs like Clients & Profits.

Q. We've checked everything and there's nothing wrong with our hardware. What now?

First, look for user errors -- primarily the way they handle padlocks, system freezes, and end-of-day shutdowns. A user who shuts off or restarts their computer while Clients & Profits is still running can damage the database. Restarting or shutting down the file server while users are still running Clients & Profits can damage the database, too. For a list of common causes of data corruption, see the Clients & Profits Database Guide.

Q. What is the Clients & Profits Database Guide?

This guide takes a straightforward approach to managing your Clients & Profits database. You'll find easy-to-understand explanations for the way the database behaves, as well as why things can sometimes damage it.

Q. Our consultant has run Norton on our file server and says its ok. What now?

Disk tools like Norton Utilities check for specific problems with hard disks, but they're not foolproof. They won't find intermittent problems caused by bad RAM, network adapters, and other upcoming hardware failures. They also won't tell you if the data is being damaged by user actions, which is more common than not. The only way to determine the true cause of data corruption is to track each incident.

Q. How do we know that Clients & Profits isn't damaging the data?

It isn't likely because Clients & Profits doesn't tell the file server how or where to save data -- which is when corruption occurs. Clients & Profits simply sends the data to the server and says “save this”, and then relies on the server to do its job.

Q. Will installing the latest update fix the damaged data?

No. Databases are damaged when something irregular happens, such as when changes to records are being saved. New updates to Clients & Profits always contain bug fixes that keep bad data from occurring, but that's unrelated to database damage. Since damaged data is caused by system and/or hardware problems, any C&P update won't make a difference.

Q. Why can't Clients & Profits prevent the damage to the database in the first place?

It's a performance issue. Any kind of data verification would substantially slow C&P down whenever anything was saved, affecting users more often. Under normal circumstances, the database engine in Clients & Profits is dependable and predictable, so the extra validation isn't justified.

Q. Why can't Clients & Profits fix any data damage automatically?

That would be risky. Repairing a database is a single-user function, so Clients & Profits couldn't even start fixing an error if it found one until everyone had quit for the day. Also, a database should never be repaired before a backup of the database is made -- just in case the computer running the repair crashes.

Q. Should we purge data to avoid adding a second segment?

No. The size of the database or the number of segments has little to do with its performance. Smaller databases aren't more reliable than larger ones; they're just easier to store and back up. Purging won't shrink the database anyway (it just doesn't grow as fast).

Q. I've download and installed all of the patches from the C&P Patches web page, but why are we still getting damaged data?

Patches only fix specific bugs in C&P in between software updates. Damaged data is caused by hardware or user errors, so it can't be prevented by software updates or patches.

Q. We're using C&P Classic. Will upgrading to Clients & Profits Pro solve our data problems?

No, because the Clients & Profits software itself doesn't affect the database's integrity. Clients & Profits Classic and Clients & Profits Pro share the same underlying structure and programming, so any data problems you're having now will happen no matter which version you're using.

Q. Our database is frequently damaged, but we only get notified by the Quick Check once in a while. Why?

The Quick Check runs only when a manager-level user opens the database. If you have only one Clients & Profits system manager, this means the Quick Check will only run at most once a day. If your system manager is a part-time consultant or infrequent user, the database will be quick-checked even less often. Making more users “managers” would cause the database to self-check more frequently. Or, the stand-alone QuickChecker utility can be used to constantly monitor the database. Why doesn't the Quick Check validate the data every time any user (not just a manager) opens the database? While the Quick Check is relatively fast, it still takes a few seconds to run. Quick-checking the database every time any user opens it would noticeably slow everyone down.

Q. We've verified/recovered the database, but it's still damaged. Why?

That's because the verify/recover utility only checks the content of the data, fixing incorrect totals and other bad data problems. It has nothing to do with the structure of the database, which is where data corruption happens. Repairing a database has no affect on the content of the data itself.

Q. What's the difference between verifying, reindexing, and repairing?

Verifying fixes bad data (e.g., incorrect account balances, job totals, etc.) but has no effect on the structure of the database. Reindexing fixes the index tables inside the database that Clients & Profits uses to find records. Repairing checks and fixes the structure that holds the database together, like the frame of a building.

Q. What's the difference between the Quick Check and the Database Utilities?

The Quick Check checks for damaged indexes, headers, and pointers that store the data in the database. The repair tools in Database Utilities do everything the Quick Check does, plus checks the integrity of every field in every record in the database -- that's why repairing takes so much longer. Are databases less reliable as they grow larger? No. Since data corruption is caused by system, user, and/or hardware problems, a small database (i.e., less than 20mb) is as reliable as a large (over 100mb) database. However, larger databases are more vulnerable to disk fragmentation, bad drive sectors, and other hardware errors. Also, as more people use the database there's a greater chance of user error.

Q. Will purging data from prior years prevent damaged data?

No. Purging data only keeps the database from growing larger as quickly.

Q. What does the "data files full" error message mean?

It could mean (1) the server's hard disk is out of free space, or (2) the database (or segment) has reached 256mb. In any case, new data is lost because there's no place to store it. If repairing can't fix it, you'll need to restore a backup.

Q. I'm repairing a file that has 187,000 records, but it's taking a long time -- the progress count is in the millions. Should I stop the repair process?

Yes. A count that high indicates that the repair tool is stuck on a particular data file because it's seriously corrupted. (Remember that the progress message isn't actually counting individual records; instead, it's an internal count.) You'll need to export/import to recover the data.

Q. How often does the database need to be reorganized?

Reorganization is needed only after installing a Clients & Profits update -- and only if there's a change to the new version's files and fields.

Q. Even if we get a Quick Check error, can we work in other areas of Clients & Profits that aren't damaged?

Yes, but there's nothing to prevent someone from adding or editing records in the damaged data files, which will make them worse. It's always best to repair corrupted data as soon as possible.

Q. How can I know if records were lost during a repair?

The Database Utilities window shows the record counts for each data file. Print the data file list before and after the repair, then compare the record counts. If the ?after? record count is lower, then you've lost data in those files. There's no way to know which specific records were lost.


Useful links:

C&P Database Guide
System Manager FAQs
System Manager Resources
System Recommendations
System Manager User Group (a list server)