Sunday, September 27, 2009

QuickBooks: Reduce Data Corruption

A client asked: What should I be doing to prevent data corruption in my QuickBooks Enterprise database?

There are a number of things that you can do yourself or in conjunction your QuickBooks Advisor:

To reduce the potential for data file corruption, take precautions to make sure the database is as stable as possible. You can do the following to reduce the likelihood of data file corruptions or other types of data integrity issues.

1) Interpreting the Product Information Window
The QuickBooks Product Information window shows valuable information about the QuickBooks program When analyzing your QuickBooks files this information is extremely important. You need to ensure your clients installed the most recent QuickBooks update, that the size of the QuickBooks file is reasonable and that the client’s lists are not nearing the 28,000 limitation for Items and the 14,000 limitation for customers, vendors, employees and other names).

2) Track Historical Database Size
A way to track your historical data use is to open a backup copy of your data file from about one year ago and note the number of transactions in the file and the size of the file. You can then note the same information using the current file. Using this information, compute the number of transactions entered over the past 12 months to project the file growth over the coming year or even over multiple years. When factoring file growth, consider not only the historical growth of the file, but the projected growth of the company and any changes the company intends to make in the use of QuickBooks. To access the Product Information window, open the QuickBooks data file and then press F2.

3) Monitor File Size
The size of the file is a major factor in corruption prevention. Though larger files do not necessarily become corrupted, the larger the file the greater the risk. The increase in risk escalates quickly as the file size reaches about 500MB for QuickBooks Pro and Premier and about 1GB for QuickBooks Enterprise Solutions. You should monitor the size of QuickBooks files and keep the sizes at or below these levels if at all possible.

There are numerous ways you can reduce the size client's data files. As one quick (and strongly recommended) option you can use the Portable Company File process to reduce file size.

4) Monitor Database Fragments
The Product Information window also shows you the number of database fragments for any given file. If possible you should have no more than 9 database fragments in the file. If there are 10 or more database fragments you can perform the following steps to reduce the fragments:

* Resort the lists in the file. Use the "Resort List" selection in the list menu to resort the lists. Resorting the lists using the list headers does not have the same reorganization impact on the file. Note: Some lists do not have a "Resort List" selection.

* Rebuild the File. Select the File menu, select Utilities and then select "Rebuild Data." You can also use the Rebuild Data utility to repair file corruptions, but the focus in this context is around the reduction of database fragments to reduce the risk of corruption.

* Create a Portable Company File and then restore the Portable Company File. This process re-indexes the database and is one of the most effective ways to reduce database fragments. You can also use the Portable Company File utility to repair some data file corruptions, but the focus in this context is around the reduction of database fragments to reduce the risk of corruption.

* Run the Windows Defragmentation utility on the drive on which the QuickBooks data file is stored (e.g. the server). The fragmentation level of the computer's hard drive can impact the fragmentation level of the QuickBooks database.

Note: According to Intuit's data repair department, running the Rebuild Data utility too frequently can actually increase your risk of file corruption. You should limit the prevention intended rebuilds to a handful per year.

1 comment:

file repair said...

File corruption is a common problem nowadays but if you can't prevent it, you can now fix it using file repair like what I did.