QuickBooks keeps all your data and most settings in a Company File which has an extension of “.QBW”. This file needs to be maintained to be healthy. If your Company File becomes corrupted depending of the severity, it could become unusable, or require some very expensive recovery.
This area most of my clients were not aware of to perform, and often I receive panic calls that their QuickBooks will not open, or they cannot add any more records, so please take the time to read this month’s QuickBooks Tips and Tricks.
To let you know upfront, most of the problems can be avoided by regular maintenance and review of key limitations. Most of these can be performed by you, and not require our services. However, we can assist you if you do not feel confident to do these on your own.
Figure 1 – QuickBooks Product Information screen provides very valuable information about your Company File.
There are several areas on this screen you should pay attention to. If you don’t you may be surprised with issues that can cause expensive services.
The first area to keep in mind is there are some different limitations between QuickBooks Pro/Premier compared to QuickBooks Enterprise. If the Top Product title included the word “Enterprise”, the product you are using would be Enterprise and not Pro/Premier. In the above case, the version in QuickBooks Premier (Accountant Edition) 2015.
There are suggested limitations for each version as far as database size, database fragmentation, and names.
Database Size
The size of the database is directly related to the number of transactions you have in QuickBooks. Some businesses grow their database faster than others. This is not only how many Customers or Jobs you do, but also how many transactions are within those jobs. If you order parts or materials for a job and they are billed to you on one bill, it counts as a single transaction. If you have multiple times these are ordered, each with its own bill, it multiplies the number of transactions.
As the database size grows, it may slow down the speed of operations. As an example, a report may take up to 20 – 30 seconds to open. In addition, as the database grows past general guidelines there is a higher risk for corruption.
General Database Size Guidelines:
- Pro/Premier 200mb
- Enterprise 300mb
In the example Figure 1, the database assize is listed at 298332 K … that roughly translates to 298mb, almost 100mb larger than the general guidelines for Pro/Premier.
Database Fragmentation
This is also an important guideline that reveals how healthy your database is. This is much like fragmentation of your hard drive. Information is not stored efficiently and access to your data is being challenged. In all desktop versions the general guideline is to maintain this at 20 or below.
The example in Figure 1 is listed at 653 ! This can easily cause issues and corruption is possible.
Figure 2 – QuickBooks Product Information screen with file size and fragmentation okay
In Figure 2 the database size is at 196436 K, and the DB File Fragments is only at 4, so one would consider the file healthy at this point. However, pay attention to the box titled, List Information, on the right side. The Customer List is 13,776 and the Total Names are 14,200.
For Pro/Premier, the combined Total Names is limited at 14,500, and Customers, Vendors, Employees, and Other Names themselves are limited to 14,500. So in Figure 2, this company file can only add a total of 300 combined of new Customers, Vendors, Employees, or Other Names until the panic message appears from Intuit to, “You have reached the maximum list limitation, upgrade to Enterprise”. Enterprise basically has no practical list limits.
Now before you decide to upgrade to Enterprise there are several other options to consider. These are too lengthy to discuss in this article, but you can always contact us through our online scheduling portal at
aeiiScheduling.com and select the
Free 1 Hour New Client Session and then select the best time for you on our schedule to review the options and suggest the best one for your business.
What Else To Do ?
Database size is a very tough one to do much about other than the condensing processes, or doing a company divide. Both of these should be handled by an experienced QuickBooks service provider such as AEII, QuickBooks R Us.
In most cases we see clients with database sizes up to 400mb with Pro/Premier have few issues as long as they follow the maintenance steps listed in the next section.
Special Note:
If you use a management system to bring in Vendor Bills and Vendor Credits, you will notice that the Bill in QuickBooks includes two entries that really aren’t needed, but increases your database size and Name Lists unnecessarily.
Figure 3 – Typical QuickBooks Vendor Bill from Management Systems using Items
In Figure 3 the Customer/Job is listed on the Bill as well as the checkbox for “Billable” checked. Even though I have provided this to the major management system companies as ill advised, they have not fixed it, or at least make it an option during setup to turn off.
The “Billable” checkbox if activated causes QuickBooks to add the Bill Items to a database table tracking expenses for reimbursement. This is not used by 95% of businesses, because it tracks your reimbursed items by Job at cost, not retail. This is used by a profession that is billing a client for reimbursed expenses such a hotel, meals, transportation.
Go to Reports > Jobs, Time & Mileage > Unbilled Costs by Job ... You may find that QuickBooks has millions of dollars listed as “Unbilled” depending how long you have used a management system with QuickBooks. These entries are increasing your database size unnecessarily.
Also the Customer/Job entry is designed for job costing individual jobs, but in most businesses, you can’t accurately job cost individual jobs without very detailed entry in your labor costs/payroll. Most businesses that have management systems utilize the management system for individual job costing and QuickBooks for month end total profitability. Hence the Customer/Job entry is not needed in QuickBooks, and also adds to the database size greatly.
Maintenance You Can Do
On a regular basis, monthly in most cases, you should not only look at the Database Fragmentation, but also check the integrity of your data, and rebuild it if needed.
Go to File > Utilities > Verify Data:
Leave a Reply