Repairing corrupted data files in QuickBooks
By Joe Woodard, AccountingWEB QuickBooks columnist
In my opinion QuickBooks is as "bug" free as any product on the market and is much less troublesome than most applications. Intuit has an incredible beta testing process before each new version and the company issues releases periodically throughout the year as users report issues and the programmers resolve those issues.
The size of the user base and the ease by which users can report issues is key to Intuit's ability to quickly resolve almost every issue in the application. With almost 4 million users and a single©\click error reporting option, Intuit is able to catalog issues quickly.
Ok...that said Intuit is a database and all databases ©\ and all applications ©\ do have issues from time to time. As Intuit Advisors we must be as equipped as possible to resolve as many of these issues as possible for our clients. The alternative is to work through Intuit's data repair department. We have had a good experience with this department in the past, but there is always an inconvenience ©\ and sometimes an additional cost ©\ involved in sending the file to Intuit for repair.
The focus on QuickBooks data repair is two©\fold ©\ involving both risk reduction and repair. This article addresses both.
Reducing the Risk of File Corruption
There are numerous steps you can take with your clients to reduce the risk of QuickBooks file corruption.
Interpreting the Product Information Window
The QuickBooks Product Information window shows valuable information about the QuickBooks program (e.g. the release level for the version you are using) and the data file you have open at the time (e.g. the number of customers, vendors and items in the file as well as the file size). When analyzing your clients¡¯ 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 (QuickBooks Pro/Premier).
Tip: Open a backup copy of your client¡¯s data file from about one year ago (as close as possible) 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 (the live file). Using this information, compute the number of transactions the client 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 like preparing their own payroll.
To access the Product Information window shown below, open the QuickBooks data file and then press F2.
Reducing Corruption Risk - 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. In our experience (and there are many variables) this 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 your clients' QuickBooks files and keep the sizes at or below these levels if at all possible.
There are numerous ways you can reduce the size of your client's data files. We will cover this topic extensively during the Scaling New Heights 2009 conference. As one quick (and strongly recommended) option you can use the Portable Company File process to reduce file size. See sections below for more information.
Reducing Corruption Risk - Monitoring Database Fragments
The Product Information window also shows you the number of database fragments for any given file. If at all possible your clients should have no more than 9 database fragments in the file. If the client has 10 or more database fragments you can do perform the following steps to reduce the fragments:
Step 1. 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 re©\organization impact on the file. Note: Some lists do not have a "Resort List" selection.
Step 2. 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.
Step 3. Create a Portable Company File and then restore the Portable Company File. This process reindexes 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.
Step 4. 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 a source at Intuit's data repair department, running the Rebuild Data utility too frequently can actually increase your risk of file corruption. It is simple math ©\ subject the law of averages. The utility is very safe and requires you to backup the file prior to the rebuild. That said, you should limit the prevention intended rebuilds to a handful per year.
Reducing Corruption Risk - Your Client's Local Area Network
The condition of the client's Local Area Network (LAN) can have a significant impact on the propensity for QuickBooks data file corruption. Consider the following:
Wireless networks are the "arch©\nemesis" of the QuickBooks data file. The QuickBooks file is sensitive to unexpected disconnections and wireless networks have a much greater propensity to have connection interruptions than connections via Ethernet cable. Strongly caution your clients not to use network QuickBooks using wireless connections. If the network has a wireless router at all, consider that some computers may have both Ethernet and wireless network adapters. To ensure the computer uses the Ethernet connection and not the wireless connection, the client should either turn off the wireless signal completely or take steps to prevent the computers with wireless network adapters from connecting to the wireless router.
General Network Maintenance
Substandard, obsolete or poorly configured routers can impact both the performance of QuickBooks in a multiuser environment and can increase the risk of file corruption. The concept here is the similar to wireless connections. If the configuration of the server or the condition of the hardware causes periodic interruptions in a user's connection, this interruption can cause a QuickBooks file corruption. Examples include connection issues with the network router, hardware or driver problems on workstation network adapters, operating system/resource issues on the server that cause QuickBooks or Windows to enter a non©\responding mode, etc.
In short, Terminal Services allows each workstation to share the server¡¯s desktop ¨C much like Desktop Sharing only much faster. When you use Terminal Services, you still need a QuickBooks license for each unique QuickBooks user, but you do not install QuickBooks on each workstation. Instead, you install one license of QuickBooks on the server and you store the data file on the server as well. Then, all QuickBooks users on the network run the QuickBooks application stored on the server ¨C up to 30 users running a single installation of QuickBooks (for QuickBooks Enterprise Solutions). This networking model is very different from the standard network configuration where QuickBooks resides on each workstation. Because the data file and the application are on the same hard drive, QuickBooks will perform significantly better and each QuickBooks user's connection to the QuickBooks data file is much more stable. In a Terminal Services environment the workstations do not need to be as powerful, but you will need to purchase a high end server. You may also prefer to use a separate server just for the QuickBooks program. You can visit the Microsoft Website or contact a local IT professional for more information about Terminal Services.
Note: Many companies provide hosting services using Windows Terminal Services. If your clients would benefit from the use of Terminal Services but does not want the initial and recurring IT costs associated with setting up and maintaining their own terminal server, consider referring the client to one of these companies. One highly reputed hosting company is Right Networks.
Repairing QuickBooks Data Files
The types of data file corruptions are numerous and the methods to repair them are even more numerous. It is in the purpose of this article to empower you to use the most effective and practical repair techniques ©\ designed to repair the widest range of issues. If the methods in this section are not effective you can contact Creative Financial Software for customized repair consultations and, if necessary, refer the file to Intuit for data repair services.
Repairing Problems Detected by the Verify Data Utility
As noted above, using in©\product utilities is the first step to repair the problem and will many times repair the data fully without additional steps on your part. When you experience a data verification issue perform the following steps:
Step 1. Resort all QuickBooks lists
Step 2. Run the Rebuild Data File utility.
Step 3. Create a Portable Company File and restore the Portable Company File
Step 4. Re©\Run the Verify Data utility to make sure the steps above worked and that the Verify Data utility doesn't detect any additional problems. If there are still problems with the file, repeat the steps above once more to attempt to remove additional problems.
Step 5. If you see the Verify Data utility after performing the steps above twice proceed to the section below.
Using the QBWIN.LOG to Get the Details of Data File Verification Issues
Perform the following steps to locate many file corruption issues and to get the details of those issues.
Note: The process below does not always locate every type of corruption.
Step 1. Select the file menu, select utilities and then select Verify Data. Proceed to Step 2 if you get a message saying "A Problem Prevents QuickBooks from Continuing" with a suggestion to run the data repair utility to fix the problem
Step 2. To see the details of the problems located during the verification, press F2 to open the Product Information window.
Step 3. With the product information window displayed, press CTRL+2 to open the Tech Help window.
Step 4. On the Tech Help window, press the ¡°Open File¡± Tab.
Step 5. On the Open File tab highlight the file called ¡°QBWIN.LOG¡± and then click the ¡°Open File¡± button.
Step 6. Scroll down on the report until you see a long, double©\line that shows the beginning of the data verification process. Scan through the verification information until you see an error or issue noted in the log and then note the information around the error like the document number, the transaction type and any amount or linked transaction that is part of the problem.
Note: The list of issues can be very long, but a very long list of issues may have a handful of common factors. For example, if there is a problem with the calculation of average cost for an inventory item, the issue may impact hundreds of transactions but repairing the problem with that single item will eliminate all of the issues. It is therefore critical for you to search for commonalities in the list of issues.
Step 7. If the corruption is related to one or more transactions you can often repair the problem by deleting and re©\entering the transaction. Search on the document number (or other identifying information) in the report to find the transaction with the problem. Then, delete and re©\enter the transaction. Almost every time, the problem will go away along with the deleted transaction.
Step 8. If the verification problem is related to a list entry, it is often a duplicate list entry and if so the QBWIN.LOG will note the duplication. You can solve this problem by renaming one of the two duplicate list entries and then (if you prefer) merging the two lists together. Note: Merging the list entries may or may not be allowed by QuickBooks depending on the list and/or the specific list entries involved.
Tip: If the QBWIN.LOG displays a duplicate list entry error for a list that is very long, export the report to Excel and use the "Subtotal/ Count" function in Excel to count the number of unique list names. Excel will render computer a "2" for each list entry that is duplicated.
Options When the Steps in this Document Do Not Work
In our 10+ years of QuickBooks consulting experience we have found the procedures above to resolve over 90% of the corruption problems experienced by our clients. That said, there is always the exception ©\ the more complex type of corruption that you cannot repair using the procedures above.
If you have this type of issue you have two options:
- Contact our office at 678©\493©\3472 to see if we can arrange a customized repair for your client. We charge no fee to assess the QBWIN.LOG file for your client and we can then tell you if we are able to repair the data or not.
- Send the file to Intuit's data recovery department. As a QuickBooks ProAdvisor it is best to arrange this data repair through the ProAdvisor Technical Support department. The phone number for this department is 1©\888©\250©\7279. General support from the ProAdvisor support team is included in your ProAdvisor membership benefits (see limitations at www.qbadvisor.com). Data recovery services may or may not be an additional charge.
Bonus Section: Imbalances on the Balance Sheet
Data corruptions can cause imbalances in the Balance Sheet ©\ and typically do so on both the cash and accrual basis. Performing the steps above will often resolve the imbalance.
If the steps above do not fix the problem ©\ and if the imbalance shows on the cash basis only ©\ consider the following primary cause:
Discounts on Payments coded to Liabilities or Equity. If you record a discount on a Customer Payment and code the discount to a Liability or Equity account, QuickBooks will show an imbalance on the Balance Sheet for the total amount of all these discounts. if the discount should post to a liability or equity account it is best to use a Credit Memo to record the discount. If that doesn't work for your client, code the discount to a clearing account (an asset account or any income statement account) and then clear the clearing account using a journal entry ©\ offset to liabilities or equity.
About the author
Joe Woodard is an Advanced Certified QuickBooks ProAdvisor and Intuit Solution Provider who has taught over 20,000 QuickBooks consultants across the country. Joe works with Intuit, state CPA societies, and Atlanta-area CPA firms to present advanced QuickBooks instruction to accounting professionals and software consultants. Joe has earned a unique relationship with Intuit as a trainer, consultant and author. Joe has built two successful accounting software consulting practices: the first in New Orleans, Louisiana and the second in Atlanta, Georgia - Creative Financial Software (CFS). In addition to consulting with small businesses, CFS provides advisory services to CPA firms and other QuickBooks ProAdvisors/Intuit Solution Providers across the country -- helping them to better service their clients who use Intuit products. Joe recently hosted the first annual "Scaling New Heights" QuickBooks conference in Atlanta.