What Are DB File Fragments and Why Do I Get Them in My QuickBooks File?

Whenever you press the F2 key in QuickBooks, a window pops up that tells you a lot of things about your company data file. One of the numbers you see is “DB File Fragments”. What’s that?

DB file fragment is basically a measurement of how much your company file changes. It reflects how much and how often QuickBooks messes with it. This is not something peculiar to QuickBooks files. With any file in Windows, as changes to the file happen on the hard drive, Windows sometimes keeps segments of data together, and sometimes Windows will assign segments of data somewhere else on the drive. That “somewhere else”, in QuickBooks, is a DB file fragment.

When you restore a portable copy, it basically plops your file’s data into as much contiguous hard drive as it can find. Then when you do stuff in QuickBooks, the data changes and expands as new records are added or even modified. So Windows pushes the file out into new sectors of the drive and creates a new ‘file fragment’.

So in a sense, the file fragments are the “tail of the dog”, simply representing how volatile the file is — and what’s going to be changing more often than a bookkeeping file?

If you defrag your hard drive with Windows, the number of DB file fragments should go down, and if you pop up your F2 window in QuickBooks, you should be able to confirm that. Then they will probably start growing just as soon as the file is used and changed.

Likewise when you create and restore a portable copy. It would be interesting to see how much it goes down by Windows defragging vs. creating and restoring a portable copy…theoretically it should be the same. This would make an interesting test to try sometime (unless one of you have already experimented with this and care to report your results?)

If the file has a huge number of file fragments, then it simply means that your hard drive is going to have to work a lot harder to pull up a report or load a screen view, and it will take longer. And when the database is being updated by a lot of users simultaneously, any kind of delay is bad both in user experience and in database stability.

One consultant I know thinks that when solid state drives are standard on PCs, a lot of QuickBooks data problems are going to go away because the whole HD read/write delay thing will go away. We’ll see.

In the meantime, I think that as long as a file performs quickly and with high stability on a given machine/network, it doesn’t matter a whole lot what the file size is or what the fragments are. The proof is in the pudding. I think people should take action to get their data supercondensed as soon as performance starts to degrade, regardless of what the file size or fragments are. And if the file has become unstable it should be repaired before it gets worse. But if everything is performing great in QuickBooks, don’t lose sleep over file fragments.

— Shannon Tucker is the President of AccountingUsers, Inc., blogs on QuickBooks topics, and has managed thousands of accounting database consulting cases. He may be reached at tucker@accountingusers.com or at 719-395-8750.