When your QuickBooks file has become too large

From Patrick Bonnaure, founder of ProLedge Bookkeeping Services

Many businesses come to a point when their QuickBooks file has become too large. How can you tell when “large is too large” and what can you do about it?

The definition of “too large” in QuickBooks is fuzzy. You can hit a ceiling based on the sheer size of the file (defined in megabytes, or MB) or by what’s in the file itself (e.g. number of items). You may have a small file in term of the amount of space it takes on your hard drive and still reach a limit based on how you use QuickBooks. The key limitations are the following:

File size
Theoretically, there is no limit to the size of your file, but in practicality, assume that you are stretching QuickBooks Pro and Premier when you get to around 150MB. QuickBooks Enterprise starts straining at around 1GB. Above these limits, QuickBooks becomes painfully slow and the database tends to get corrupted easily.

Number of items
There is a limit to the number of list items (classes, customers, jobs, classes, etc.) that you can have in your file. This limit is different for each type of item and for each version of QuickBooks and it changes year to year, so it’s a hard target to keep track of. Here’s a good rule of thumb to use:  if your total number of items is below 10,000 overall, you’re safe with QuickBooks Pro and Premier. The same number is 100,000 for Enterprise. Above these limits, you need to get more granular and look at your individual items types. Hit F2 when you are in QuickBooks and it will give you a count of all your items. For more details on the limits per item type, refer to this table: http://support.quickbooks.intuit.com/support/articles/INF12412

Shrinking the size of your QuickBooks file

Let’s assume that you reached the conclusion that your file is too big. What can you do? First and foremost, you need to decide if you maxed out QuickBooks Pro and Premier and you need to upgrade to Enterprise. If you have reached the limit on number of items, you can try to cleanup the  items that you don’t  use anymore (see more on this below), but if it doesn’t have a sufficient impact, Enterprise might be your best option. I recommend that you first try a cleanup, because Enterprise represents a significant jump in price from Pro or Premier. Getting a second opinion from an expert is not a bad idea at that stage, just because of the cost involved. Don’t bother asking Intuit because they have an incentive to get you upgraded to Enterprise.

If you decided to try to decrease the size of your file, you have 3 options:

Use QuickBooks’ “Clean Up Company Data” feature to condense the past years.
This feature replaces most of the transactions from the previous years by much smaller monthly journal entries. The details on your previous years are gone, but the P&L and balance sheet “should” remain intact. See some disclaimers on this feature below.

Use QuickBooks’ “Clean Up Company Data” feature to remove ALL transactions.
The same Clean Up feature mentioned above also allows you to create a new company file without any transactions left, but with all your lists of items intact (customers, vendors, etc.). In this scenario, you need to manually recreate the opening balances for all your accounts, starting from a certain date, including open invoices, open bills, inventory, bank accounts, etc. It can be hard work. It’s as if you started with a brand new company file, exept that you don’t have to recreate your item lists.

Start a brand new company file
This is the brute force approach. It requires you to recreate all the opening balances and all your item lists. HOWEVER, don’t necessarily discount this option. It may make complete sense in a number of scenarios. Here are some examples:  

Your current item list is unnecessarily bloated. The previous 2 options carry over your old lists. Starting afresh is sometimes faster than deleting old items, which in some cases is not possible anyway.

Your database has become corrupted and you can’t seem to be able to fix it.

You have messes carrying over from previous years that would take a lot of work to fix (broken sales tax liability, open invoices that should have been closed a long time ago, etc.)

Keep in mind that there are 3rd party tools that allow you to migrate lists and transactions from one QuickBooks file to another. This is sometimes an elegant, albeit complex, solution.

A word of caution on the “Clean Up Company Data” feature in QuickBooks. This feature doesn’t work well unless you select the option to delete ALL transactions. Even Intuit is gun shy about this feature and seldom recommends it. The good news is that they seem much to be much more confident about this feature in the 2012 release that will come out in the Fall of 2011. They apparently made a breakthrough. If you are not in a rush, I’d wait for this new release before making big changes. The issues with the Clean Up feature are as follows:

  • Sometimes, it simply breaks, ie it alters the financials for your previous years. The only way to make sure this doesn’t happen is to do very careful backups and archives of your file and create as many reports as possible before and after the cleanup and compare them, both on accrual and cash basis.
  • In many cases, it doesn’t shrink the size of your file all that much, because it has an algorithm that tells it that certain transactions cannot be deleted (e.g. open invoices, inventory transactions linked to current items, etc.).

It doesn’t hurt to try this feature, but proceed with caution: create backups and compare you data before and after. When you create a backup, make sure to rename the backup and save it in a separate Archive folder. This a very important backup because it’s the last version that contains the details of your previous years. You don’t want to lump this with your generic daily or weekly backups. Also, don’t forget to note the password for that archive version. Your passwords of your “live” QuickBooks file changes over time and you want to make sure that you can reopen that archive file a few years down the road in case you have an audit, for instance.

Post a comment

*