QuickBooks® Desktop: Database/File Maintenance - What Is It and Why?

What is the purpose of QuickBooks File Maintenance?

In order to ensure your QuickBooks Company Data file is ‘healthy’ and to avoid or repair data corruption, File Maintenance is performed in a series of potential processes and steps.

How do you know if there is QuickBooks File Corruption?

Common symptoms of QuickBooks file corruption are:

• Memorized reports don’t run, or produce the wrong reports. For example, a memorized Accounts Receivable report is run, it produces a Sales by Item report.

• Balance Sheet report is ‘out of balance’ – either ‘cash’ or ‘accrual’ basis or both

• QuickBooks ‘quits’ suddenly when editing a specific transaction. For example, every time to edit a specific invoice, QuickBooks ‘quits’ or ‘crashes’ when you save the invoice.

• Data looks incorrect and doesn’t make sense. For example, suddenly your Accounts Receivable report doesn’t match the Accounts Receivable balance on the Balance Sheet.

• The link between a Purchase Order and Bill doesn’t work (when trying to go from one to the other), or other similar transaction link problems

• Reports are suddenly taking an unusually long time to run

• Balances on the Balance Sheet do not tie to the ledgers, e.g.

  • bank balance on the Bal Sheet doesn’t match the bank ledger

  • AR Summary balance doesn’t match the AR balance on Bal Sheet

What types of data errors are reviewed during file maintenance?

• Ensure all balances are supported accurately by the transaction (e.g. AP activity adds up the AP balance).

• Ensure transaction links are accurate (e.g. the link between a Sales Order and its Invoices).

• File maintenance also checks that no duplicate list entries (e.g. Chart of Accounts, Customer) have erroneously occurred.

Why would any of these errors exist?

QuickBooks Company File corruption can occur due to many reasons. It is often quite difficult (if not virtually impossible) to track down the specific set of variables and steps that may have caused the issue. Some of the factors may be:

• Network glitches on your infrastructure, interrupting partially saved data

• QuickBooks software ‘bugs’ – programming logic that doesn’t properly handle a series of steps or activities properly

• Infrastructure (server or file server) interruptions

So, instead of spending energy on trying to figure out what happened, routine maintenance can help the company proceed.

Certain Risk Factors that make corruption more common:

Below are some of the risk factors that make QB file corruption for common. If you have none of these risk factors, monthly or even quarterly file maintenance is probably sufficient. As you add on risk factors, you may want to perform file maintenance more often.

1) File size: Companies start to see more database errors when their QB file size exceeds

a. >150 MB for Pro/Premier

b. >1.5 GB for Enterprise

If this is the case, we recommend you seek the assistance of a qualified QuickBooks ProAdvisor to help you with your options for upgrade (if on Pro/Premier), file size reduction or a fresh QuickBooks file. Many options are available.

2) Number of Simultaneous Users: If your company file has numerous simultaneous users, it is more likely to have corruption than if you have only 1 or 2 users

3) Using many ‘linked’ transactions (e.g. Sales Order/Invoice/Payment, Purchase Order/Vendor Bill/Payment). The more transactions that are linked to each other, the more opportunity for those links to break

4) Installation type: If your company uses QuickBooks on a local network, with the software on separate PCs and the database on a server, there are more opportunities for file corruption due to local network glitches or software inconsistencies between workstations. If your QuickBooks is hosted on a terminal services environment (e.g. RightNetworks, Trapp Technology or Swizznet), there is only one instance of the software and no local network between transaction and database, so the opportunity for corruption decreases. Anecdotally, our clients with hosted environments deal with significantly less file corruption. But be aware, Intuit continues to encourage the standard local server and installation environment, and unique installations can make it more difficult to support. So, as always, infrastructure decisions have their trade-offs, and there is no one perfect answer.

Some of the above risk factors are unavoidable, due to business requirements and full usage of QuickBooks features. But it is helpful to understand that these risk factors can impact the frequency of preventative maintenance you might want to perform on your QuickBooks file.

What are all of those files that sit with my QBW QuickBooks file?

There are multiple files, but the most important and commonly seen are listed below.

• .QBW file extension – the main QuickBooks company ‘database’ file

• .TLG file extension – the log file – all activity since the LAST full backup with full verification (this file grows until you perform file maintenance)

• .ND file extension – the multi-user tracking and management file

• .QBB file extension – a QuickBooks backup file

What type of Information should you retrieve about your QuickBooks File before starting file maintenance?

QuickBooks desktop (Pro, Premier or Enterprise) consists of 3 main components, in most implementations.

• QuickBooks desktop software – This is the software application that the users sees, the windows, the functions, etc.

• QuickBooks database manager software – this is ‘behind-the-scenes’ software that manages the multiple users using the same QB File.

• Company data file – This is the actual ‘database’ of your QuickBooks data. There are several files that are related to the database; however, the main file has the “.QBW” extension. This .QBW file contains all of the transactions and lists associated with your company.

Before file maintenance begins, the following information should be documented:

• File location (folder, filename)

• File size

• # of fragments

• Log file size (.TLG file size)

The best way to find out the file size and fragments:

• Open QuickBooks and logon to the company file

• Press CTRL-1 (CTRL key and the number one)

• To find the .TLG (log file) size, look in the folder on your local drive or server that contains the QuickBooks file and note the size of the file in KB:

What are the primary activities that might be performed in QuickBooks File Maintenance?

• Backup with Full-Verification

• Rebuild

• Manual Data correction

What does the Backup (with Full Verification) do?

The Backup (with Full Verification) will first do a “verify” step to verify the data integrity. Once the data is determined to be valid, a backup (.QBB file) is produced. The .TLG log file is then emptied, since the .TLG file only contains log data since the last verified backup.

Are there any cautions?

The Backup (with Full Verification) does not alter the QuickBooks data, so our experience is that this is a fairly low-risk process to perform.

Key items to be sure you pay attention to are:

• Know exactly where backups are saved and be consistent about saving the backup files in this location

• Make sure the location you are saving the backup to has adequate free space.

• If the pre-backup verification fails, you MAY want to proceed with the “Rebuild” step explained below, but you will want to be sure to understand the risks and cautions before proceeding.

What does the Rebuild do?

The QuickBooks Rebuild attempts to correct any data errors found during the file verification process. Some examples of the types of things that the Rebuild might repair

• Broken memorized reports – if they can’t be repaired, they are deleted, so be prepared to re-create memorized reports

• Un-linked transactions – for example a bill that isn’t properly linked to its payment

• Balance Sheet Out-of-Balance

• Broken Lists (e.g. customers, vendors, items)

Are there any cautions?

YES! Be cautious!

If you run a Rebuild and it doesn’t complete successfully, it can further (and irreparably) corrupt the data file.

How do you know if the Rebuild completes successfully? You will receive a message that the Rebuild completed, and a list of errors that were fixed (and possibly errors that cannot be fixed).

How do you know if the Rebuild didn’t complete successfully? This happens when the program process is interrupted and you do NOT receive a final message of completion. Some interruptions look like the below. But ANY interruption before the Rebuild completes is considered an unsuccessful Rebuilt

What if there are errors that cannot be corrected with the Rebuild?

The Rebuild will sometimes complete successfully, but it will report to you that not all errors could be fixed. In this case, manual data corrections are sometimes necessary. This involves reviewing the Rebuild log and looking for information about the specific errors. This is tedious and difficult to work through, and we encourage you to reach out to a seasoned QuickBooks ProAdvisor with experience in this area.

Manual corrections can include:

• Delete and re-enter a ‘broken’ transaction (invoice, bill, etc.)

• Update and ‘save’ an existing transaction

• Merge two list entries (if there are duplicate list items) or rename one

• Create an inventory adjustment, back-dated, to cause inventory to recalculate. Delete the adjustment.

Where to go for help:

To learn more about the actual performing of File Maintenance, see our next BLOG titled “QuickBooks Desktop: Database/File Maintenance – How To Perform”

At any time that you find QuickBooks File Maintenance to be too risky or overwhelming for your in-house or IT support staff, we encourage you to obtain outside help. Below are some options, and we recommend proceeding in the order listed below:

Option 1: Your company’s IT support firm: Your IT Support firm may have expertise in QuickBooks, and we suggest you start there. They fully understand the technical architecture and components, so if they have QuickBooks expertise, this can be a good solution for you. But many IT companies do not specialize in application database maintenance, so it is understandable if they do not have QuickBooks file maintenance expertise.

Option 2: A QuickBooks ProAdvisor Firm: The right certified Intuit ProAdvisor (or firm) can be a good option. QuickBooks ProAdvisors are not Intuit employees, but are certified by Intuit and often bring unique and custom solutions to the table for their clients including routine file maintenance for QuickBooks customers. A ProAdvisor may be able to help without losing time. There are also some QuickBooks repair services that a ProAdvisor can lead you to, for timely repair, if the fix is beyond the ProAdvisor’s capabilities. Some ProAdvisor firms provide routine maintenance as a service and have significant experience in working through the various error scenarios. Our firm does! www.vectorbusiness.net

Option 3: QuickBooks Support: Intuit’s internal Support team can sometimes help fix specific data errors. But their solution is often to ‘send in the file’ for maintenance, and your business may not have the ability to stop QuickBooks work for multiple business days. Intuit’s internal Support does not provide routine file maintenance as a service (as of the time of this blog writing).

A Final Note:

Not performing routine QuickBooks file maintenance can be risky. So, if QuickBooks is critical to your business, we encourage and recommend routine file maintenance.