Monday, October 24, 2011

QuickBooks: File Size & DB File Fragments Matter

The size of your QuickBooks file and the number of DB File Fragments determines the health of your QuickBooks company data file. Most people don’t periodically check the health of their QuickBooks file to help prevent data corruption – mainly because they don’t realize that they should or even how to go about it. However, it is important to periodically check this information just to see how healthy your file is or isn’t.

To check the size of your own QuickBooks file – with QuickBooks open press the CTRL and the 1 keys at the same time OR press the F2 key at the top of your keyboard – this will open a Product Information window.

There are three important things to review:
1) File Size for QuickBooks Pro or Premier, files around 150 MB may start to experience some problems. If your file hovers around 250 MB, it may be time to consider starting a new data file. Note that QuickBooks indicates the file size in “K” while the recommendations are in “MB”. The general conversion is to move the decimal to the left three places. So, file size 180932 K is roughly 180 MB - a fairly large file; this is confirmed by the fact that there are 17583 total transactions in the file

2) DB File Fragments – Less than 10 is recommended, but anything under 20 DB file fragments means that your file is pretty healthy; while anything over 20 means your file is sick!

3) List Limits – In QuickBooks Pro and Premier, you can only have 10,000 accounts, 14,500 total names or 14,500 items. If your company needs more list capacity, then an upgrade to QuickBooks Enterprise Solutions may be needed. Otherwise, creating a new file provides an opportunity to clean up the lists and delete things.

There are some things that you can do to try and repair you QuickBooks file yourself:
• Rebuild you company data file and check the Product Information Window after to see if the number of DB file fragments has been reduced
• Create and restore a Portable company file and check your results
• Seek the help of a professional to repair your data file or create a new one.
Keeping your QuickBooks data file healthy means that it will perform better!


Anonymous said...

You stated that file sizes over 150K for QB Pro/Premier may start having problems, what about for QB Enterprise???

Anonymous said...

I upgraded to QB 2011 on my mac back in november. Recently, I closed my business file to make a new company, when I tried to reopen my business file it said that I needed to upgrade the file to 2011… so I hit OK and now my business starts 11-10-2010.

I have searched my hard drive for any other files and can't find it. Where did my information go?


I tried the forum but no luck.

Thanks in advance, Michael

Cynthia Huber, CQP said...

Regarding the file size for Enterprise before issues arise: It depends on several things — including the server, network, etc.

If the data file is between 500 MB – 1 GB and you’re having performance problems, then you may want to create a new, smaller file with just the last year or so of data.

Cynthia Huber, CQP said...

As a follow up to the question about Enterprise, I think that many of you may be interested in knowing the other maximums:

Chart of accounts 10,000

Items, including inventory items
(Group items can contain only 20
individual items) >100,000
(29,000 in version 6.0 and earlier)

Job types 10,000

Vendor types 10,000

Customer types 10,000

Payroll items 10,000

Price levels 100

Classes 10,000

A/R and A/P terms total 29,000

Payment methods 10,000

Shipping methods 10,000

Customer messages 10,000

Memorized reports 29,000

Memorized transactions 29,000

To Do notes 10,000

Total names:
employees, customers, vendors, other names, combined >100,000
(29,000 in version 6.0 and earlier)

Sales reps 10,000

Sales tax codes 10,000

Cynthia Huber, CQP said...

Mike, I will reach out to you directly.

Joe Aschoff said...

I'm at my wits end. Since upgrading to 11.0 and 12.0 QBES (I upgraded to fix the problem but didn't), I've been encounter a painful issue. In Multi-user mode, once another user saves a transaction, every user experiences a pause before they can access the data file. It doesn't matter if they're opening the customer center, or just hitting "create invoice," the system looks as though it is refreshing all the lists and hangs for 30 seconds.

I've spent much time with Quickbooks support and have tried resorting the lists, renaming the TLG file, making a portable company, rebuilding the data file (it passes the verify without a problem) and most recently, I've given my file to data services and they deleted some corrupt transactions. It didn't fix it at all.

Here's the stats on my file: File size 78396k, Page size 4096, Total transactions 3096, Total Targets 33138, Total Links 21339, Dictionary entries 15, DB File Fragments 10, Schema version 72.18.

I'm at my wits end about this, and any help anyone can offer would be greatly appreciated.

Cynthia Huber, CQP said...

This could be a few things, and since I don’t know all that Intuit has been tried - I’ll mention a few things that were presented to me from an excellent IT resource that has seen many of the same issues. Clearly there is a ‘bottleneck’ somewhere.

I'm going to do this in 3 postings in order to meet the blog character restrictions.

1) File Size:

• File size is small at only 78396K (roughly 78MB) so this should not be an issue, unless the ‘physical machine’ acting as the QuickBooks server is also performing a lot of other tasks, and has an extremely limited amount of RAM.
• If you are running a 32-bit OS on their server, with a maximum of 4 GIG of RAM and a lot of other server functions are going on, the QuickBooks server may not even be running the ‘maximum RAM’ it can use because there is insufficient RAM to meet all the requirements of the server.
• While the QBES Database Server is intended to use 256 of RAM minimum and 512 of RAM maximum, I have seen where it is actually using less than half of that when too many other windows processes or applications were running on the server. Someone would need to monitor performance using all Windows Task Manager Flags to see if there are memory use limitations.
• You also need to be sure that the QBES Database Server utility is properly configured (properly update for version of QB) and the files scanned.

2) Hard Drive
• Hard-drive speed/capacity etc. is also critical in QB. Most people buy the ‘biggest drives’ but not the ‘fastest’ they can afford; actually smaller very fast drives are preferred for QuickBooks (a small 15,000 rpm about 4 times the size of the QBW file is really the best) when that drive is devoted solely to QuickBooks.
• We are now seeing ‘solid-state drives’ for just such purposes (sole-application performance) Read/write speed (I/O) is maximized and performance improves substantially.

Cynthia Huber, CQP said...

This is part 2 of my response.

3) Network Hardware

• Your network hardware can also play a roll in this. If any machine is working at less than 1GB (1000MBPS) network speed, then in reality the whole network is running at less since they are all communicating in multi-user mode.
• So network cards, hubs, ports, switches, routers should all be checked.
• Important…. ‘no’ wireless connections to QB at all.

4) Fragmentation
• Another cause of poor performance has to do with ‘fragmentation’ of the database and/or hard-drive upon which the data is stored. The DB Fragments in the F2 window are simply a measure of database fragmentation, but if a hard-drive is badly fragmented, there are prolonged periods of search and write times which increases wait-time.
• Your IT professional needs to consider ‘defragmenting’ their hard drive, even if windows defragmenter says it doesn’t need to be when tested.
• IT professionals working with QB issues almost always see improved performance on a freshly defragmented drive if all other parameters remain constant.

Cynthia Huber, CQP said...

This is part 3 of my response.

5) List Sizes
• Even though you didn’t mention ‘list sizes’, with only 3096 transactions, I doubt that lists could be very large unless you ‘dumped in’ a lot of names from some other file source or 3rd party application.
• So really I doubt that this is ‘file’ based, it doesn’t sound like real corruption in the actual data file; but there is one way to check (although it isn’t always a sure sign because the file sizes are so different).
• You can perform the same kinds of steps, in multi-user mode, with several users logged-in using one of the ‘QuickBooks Sample files’ (you will probably have to configure extra users in that sample) to see if they are getting the same kinds of ‘pauses’, if so then clearly the bottleneck is either ‘environmental’ or ‘QuickBooks-application’ related (not file related).

If all of 1, 2, 3, and 4 are in good shape, and you get the same results with a sample, then run the Windows “Repair” of QuickBooks installation on every machine including the server. You could have used one of the Intuit installation ‘disks’ for install that had a glitch that has not been corrected even by updates. If repair doesn’t do anything, or fails, you should uninstall and reinstall from a download of the application and not the CD version.

Good luck and please keep us updated!