The Clean Up Company Data… utility performs 2 functions:
- selectively summarizes closed transactions by creating new general journal entries by month and deletes the summarized transactions
- selectively removes unused list items, including accounts, items, customers, vendors, other names, and To Do notes
The Clean Up utility can accomplish several important goals:
- improve performance by reducing the size of the company file
- improve efficiency by removing stale list items
- reduce recording errors by reducing the chance of assigning a transaction to the wrong list item
It can be used to remove transactions as of a specific date or to remove all transactions.
QuickBooks will not remove a closed transaction if it has 1 of the following attributes:
- it is an uncleared bank or credit card transaction
- it is marked To be printed
- it is an invoice or estimate and marked To be sent
- it contains unbilled costs
By default in the clean up process, closed transactions that meet any 1 of these 4 criteria will not be summarized and deleted, but these defaults can be changed. We don’t recommend changing these defaults, with the possible exception of transactions excluded because of the To be printed or To be sent flags.
Unused list items can be removed from 1 or more of some important QuickBooks lists as part of the clean up:
The Clean Up utility will complete 4 steps:
- Make a backup
- Verify the company file (.qbw)
- Create an archive copy of the company file (.qbw)
- Clean up data
Just before starting the clean up, QuickBooks will describe what it is about to do and ask you to proceed with the clean up:
During the clean up process itself, you’ll see the following messages, indicating that processing is being performed:
Once the clean up process has successfully completed, you’ll see this information window:
Running the Clean Up utility has a significant impact on your company file. See our related article on the effects of running the Clean Up utility.
For more information on the Clean Up utility, consult this Intuit knowledge base article.
(No Ratings Yet)
Loading ...
Looking for discounts on QuickBooks? Check out our
Our QB file hasnt been cleaned up since 2003. its about 500MB.
I ran the clean up utility after closing the to 12/31/2007.
it does backup and rebuilding, when it gets to the cleanup process, it gets stuck at 21%.
the program stops responding after that.
is it normal to take morethan 45 mins at that same %?
Not an expert on QB but i followed similar instructions as above.
runing qb 2006 enterprise.
thanks in advance
Mike, if you were running Pro or Premier, your QB file would be about 5X bigger than the recommended maximum. See our article on file size limits, about 1/2 way down. For QB Enterprise Solutions 2006, you should be ok in that area, so we’ll have to investigate other things. You mention that it backs up and rebuilds. I think it actually verifies and backs up, but doesn’t automatically rebuild. (I don’t have QB ES 2006 at my fingertips so I can’t check my memory.) The fact that it stops before completion suggests one of several things:
The suggestions in the first recommendation might seem strange, but there is sound reasoning behind them.
Let us know if that helps.
Hi
I did the following and here are the results:
1- i ran the rebuild twice
2- went through the cleanup process (closed company date to 12/31/2007)
3- check all the boxes to remove everything before the above date
it took over one day and finally finished, However, nothing was cleaned up.
still had transactions since 2003.
did i miss anything in the process?
Mike – Read through some of the prior comments on areas to check. Since clean up completed but didn’t clean up anything, that suggests there are open transactions. There is a long list of places to check for open transactions (many are outlined in prior comments), but in summary you need your bank and credit card accounts reconciled and you need vendor and customer payments completely applied to bills and invoices, respectively. Open transactions are what is blocking the clean up from removing transactions. Unfortunately, QuickBooks doesn’t offer a report that outlines what is open, so it can be challenging to find the blocking transaction(s). After you try some of the other items described in earlier comments, let us know your results.
Even though I am using QB Pro 2008 multi-user version, your experience with QB 2010 is the same as mine. None of my transactions were “cleaned up” except for those that did not involve inventory (expense items).
My understanding of this q2q software that I used in 2007 was that it was for balance transferring – and that is exactly what it did – I was able to “bring in” 6 months worth of data. Transactions WERE removed with that software and just a balance forwards brought into the new file. It worked except that there were many steps regarding reconciling bank accounts all over again, it opened all purchase orders, and just many, many cumbersome steps involved.
The custom transaction report with date set to ALL is worrisome to me. I would have to run this report when other users are out of QB, and I fear it would take a long, long time to run or maybe lock up my QB. There are many reports we cannot run in multi-user format because they just take too long.
I do not believe there is a way on our version QB Pro 2008 to remove all transactions. Is this function on a later version, and does it actually work? If so, I’m tempted to upgrade, then start a new file and just remove all transactions, making my users have to go back and forth for a while, but at least reducing our file size.
Please let me know what you find out with your “digging”
Deanna
Deanna – You absolutely can remove all transactions in QB 2010. Take a look at the first screenshot in this post; just click on it for a better view. That’s a radical approach, though, and it would need to be done carefully.
After more digging with QB 2010, we were able to get QB 2010 to remove customers, vendors, invoices, and vendor bills, provided that the correct checkboxes are selected. However, QB will never remove an inventory part or service item from the Item list. We’ll post more about this.
As you have discovered, in a large file the problem is that it’s difficult to identify which transactions are open. There is a related problem (which might not be an issue for you) that inventory items are never cleaned up. If your business has a lot changes to the item list, you could easily bump into the limit on inventory items in QB Pro or Premier.
To get your QB file to a much smaller size and get back to reasonable performance, you have 2 choices:
create a new company file of open transactions; this is normally done at the start of the fiscal year, and if you’re doing a lot of inventory transactions, it’s a project that you’d have to re-do every few years;
upgrade to Enterprise Solutions; the database engine in ES is better suited to bigger files and will provide better performance in your circumstance
You may find it a good idea to upgrade to ES and start fresh with only open transactions, because if inventory transactions are brought into ES they won’t be able to be removed.
Since you said you’re running a multi-user system already, the upgrade to ES might not be as expensive as it first appears, since ES starts with a 5-user license. If you want to explore possible discounts on an upgrade or how we could create a new QB file for you, just send us a note on our contact page.
We’re recently seen something in QB 2010 that we haven’t observed before: cleanup failing to produce expected results when it comes to inventory items. For the sake of simplicity, we created a QB 2010 company file with a simple chart of accounts, 1 inventory part, 1 vendor, and 1 customer. We bought 10 units of our 1 inventory part from our 1 vendor, sold them to our 1 customer on 1 invoice, received payment in full, and deposited the proceeds in our 1 bank account, which we then reconciled. Based on Intuit’s description of cleanup, we’d expect that the invoice, payment, and customer would be cleaned up. However, none were, no matter how we changed the cleanup settings. Time for more digging …
Chief, I reconcile all bank statements and credit card statements every month – they are up to date! We may have some “unbilled, unreimbursed costs” but we rarely enter a customer name there, so if we have any, would be few.
I clicked EVERY box I could for the “clean-up.”
My current QB file only goes back to 6/1/06. On 1/1/07, I used some special software to transfer balances from an older file and start a new one (don’t wish to do that again, it was a nightmare). So open links as of 5/31/06 would be all inventory in the system. There are no unreconciled accounts going back to June 06.
We do have invoices in the print queue, but they are only at most a month old (we use that function to make sure invoices are turned in to the office).
Yes, our QB file is very large and growing (and slowing down) every day. I’ve heard differing reports. Will “clean-up data” actually reduce the file size? I’ve heard that it will not. Why does QB not provide a way to reduce your file size? We should be able to start a new file each year and just keep the data we want without all this trouble, and move whatever data we want into an archive file.
I do not understand what a .TLG file is? How do I check the size of this file? What do you mean by “manual backup from within QB”? I have done some back-ups (when I was verifying, rebuilding, cleaning up). It makes you back up first. I backed-up to another file on the Server, but it takes over an hour and has to be done at night in single-user on the Server with no other computers on. Normally we have an outside company that backs up our data file nightly and automatically.
Our DB file fragments are 93. Are you suggesting that I just run a de-frag on our server?
Thanks for your help!
Deanna
A .TLG file is a transaction log file that stores transactions since your last manual backup. You can find it in the same folder as your company file, but with a .TLG extension. The .TLG file grows along with your company file until you do a manual backup from within QB. For a manual backup from within QB, see this article.
The clean up utility absolutely will reduce your file size, but until you solve this inventory issue, you won’t experience the full benefit. I do understand your frustration. Finding the transactions that are blocking cleaning up inventory can be very challenging. Intuit really needs to add a report to the clean up utility that identifies the open linked transactions that it found in the cleanup period. Otherwise, you’re stuck fixing 1 problem, re-doing the cleanup and crossing your fingers hoping for the desired outcome.
You indicated you ran some transfer software years ago. It’s possible that the transfer software left open links that you can’t fix in QB itself. If you want to try to solve this yourself, you can create a custom transaction detail report, and set the Date range to All. Design the report so that all of the available fields are visible. Look at the oldest transactions first, and try to spot differences (such as in the Paid status). That’s the way one would go about trying to identify the transactions that are open, because at this point we’ve covered all of the general trouble spots.
DB fragments of 93 is not completely out of range in a multi-user environment, so I’d leave that for the moment.
Keep us posted.
Chief:
More data which may be helpful:
File Size: 544,588k
Pages: 4,096
Total Transactions: 103,923
Targets (?): 504,714
Links (?): 327,451
When I do a Control 1, there is a box that gives Clean-up statistics:
Last run: 8/12/10
Removed as of date: 12/31/08
Last payroll deleted: 12/31/08
Last inventory deleted: 5/31/06
This also seems to point to inventory issues as to why no customer invoices appear to be removed in my file before 12/31/08. I need to remove these because having to search through all this data slows down our 3 users (file resides on server) substantially.
Thank you.
Links between Inventory parts and other transactions are definitely the source of your clean up problem. You can see that with the difference between the “Removed as of date” (12/31/08) and the “Last inventory deleted” (5/31/06).
Some things to check: bank and credit card reconciliations. Have you reconciled all bank and credit card accounts through the clean up period? If you haven’t, the link is open and nothing in the chain will be cleaned up.
Have you ever left the Billable? flag checked? This is the checkbox to the right of each line item on a check, bill, or credit card transaction. Whenever you enter a customer name on a line item of one of these forms, the Billable? flag is checked by default. Doing that would create unbilled (unreimbursed) costs.
When you ran the clean up, did you check the boxes on the Additional Criteria screen to Remove transactions marked To be printed, Remove invoices and estimates marked To be sent, and Remove transactions containing unbilled (unreimbursed) costs? These checkboxes will cause more transactions to be cleaned up, but make sure you’ve billed all reimbursed costs in the cleanup period. Also, make sure to check as many of the boxes as possible on cleaning up unused list items.
Since you know the cleanup found open links after 5/31/06, what’s different after that date? Do you have unreconciled bank or credit card accounts in June of 2006? Do you have invoices in your print queue? June of 2006 is your bottleneck, but changing your cleanup settings might work.
Your QB file is very large. Probably why you are trying to clean it up … Ideally, this should be 100 Mb or smaller. While you are sorting out the clean up problems, you can do some things to improve performance: 1) check the size of your .TLG file. If it is large (> 50 Mb), run a manual backup from within QB. Do this even if you have other backup routines. 2) Check your DB fragments on the same screen where you gathered the info from your previous comment. If they’re a large number (> 30 for example), I’d explore de-fragmenting your disk.
Let us know how that works.
Chief: That particular bill had an inventory item called “sp ord” – we use that inventory item for “special orders” of items we buy and then sell but don’t stock (unfortunately we do that quite a bit) – we currently have 3,883 items, 1,507 customers and 374 vendors.
The inventory level for sp ord can fluctuate during the year depending on when/what we buy and then invoice. Right now sp ord is at 12 but does have negative numbers sometimes. In fact, because we have many similar items, some items can have a negative number at times because a salesman has used the wrong item number on an invoice. However, on 12/31 of each year we make inventory adjustments after taking physical inventory and sp ord ends up at 0 which it did on 12/31/08 (the cut-off date I used for “clean-up data”).
We do use estimates, and many times those are not turned into invoices. How can I “close” them without deleting them? There are not a lot, though, maybe 100 over the past 3 years.
How does inventory ever get “closed” – we bring it in, invoice it, sometimes write it off as a loss or give-away and try to make sure it is accurate on the last day of each year.
We have QB 2008 and my boss ran the clean up data utility recently but it did not delete or summarize any of our customers invoices or transactions. We have the years 2006 to 2010 in the file and she choose to “clean up” the years 2006 thru 2008.
The transactions were not any of the following: an uncleared bank or credit card transaction,
an invoice marked “To be printed”, an invoice or estimate marked “To be sent” or an invoice with unbilled costs.
Any ideas on why this occurred? Does it have anything to do with inventory?
Thanks for your help!
Tami – The best way to troubleshoot this is to compare what did get cleaned up and what didn’t. First, check to see that consolidating general journal entries were recorded by month during the cleanup period. That will be evidence that the Clean Up utility did something. If it did nothing, it might be time to check out the Verify and Rebuld utilities.
The Clean Up utility won’t summarize invoices. Instead, it will summarize the dollar amount of the transaction. List items are removed based on your selections in Select Unused List Items To Remove. Your comment says that you can’t find any customer cleaned up. Did your boss check any of the list items in this window? If your boss didn’t check Customers, that’s the reason why no Customers were removed.
Were the invoices paid in full? If they weren’t paid in full, they won’t be cleaned up. You said the transactions weren’t an estimate marked To be sent. There is a glitch in QB 2009, discussed in this Intuit knowledge base article, involving closed transactions linked to an Estimate. Did your customer invoices originate from Estimates? Unfortunately, this glitch is still an unresolved error.
Finally, only transactions that don’t have any link to an open transaction are removed. If you use the Undeposited Funds account but have payments that you have not deposited from a customer payment, the customer invoice won’t be removed in clean up because it is linked to an open transaction (the undeposited funds). Normally, you’d catch this because your Undeposited Funds account would build up a large balance. We’ve seen a lot of situations where the Undeposited Funds account wasn’t managed properly even when financial statements and bank reconciliations were otherwise accurate. The important thing here is to think of any transaction that might be open and linked to the transactions that you expected to be removed but weren’t. If everything else is ok, those links might be what stopped the Clean Up utility from delivering the desired results.
Those are a few things to try. Post back with some more info so we can help you get to the bottom of this.
Hi Chief! I’m Tami’s boss and we really appreciate your trying to help us, this is very frustrating. We are now using QB Pro 2008. In Jan. 07 I did clean up our data by using some purchased software that brought balances forward over as of 6/1/06 – but it was a very time consuming (and expensive) process so was hoping “clean-up data” would be easier – NOT so. In just a couple years, our file is huge again. I’ve run Verify and Rebuild many times (in fact, had to do it several times before the Clean-up Data would work). Almost all invoices have been paid in full (or they were written off as bad debt at year-end), we do use Undeposited Funds but deposit out of that account daily (only balance in there is today’s deposit).
At first, all we noticed about “clean-up” was that a few customers we had just put in (but didn’t have sales yet) were entirely removed so we had to put them in again – I checked everything because I wanted to clean up as much as possible.
Since then, I’ve discovered that many vendors do have all data before 1/1/09 removed from their “card” – mostly this is vendors for non-inventory items. I discovered one vendor (a fuel company) had all data before 09 removed except for one bill – it happened to be an inventory item and not fuel!
We originally set up our accounts with some special COGS accounts we made up just for us (per my Accountant) so we do not use the QB default COGS account (but do GJ entries at year-end). I’m beginning to think this is the cause of all our problems – we have a LOT of inventory items. And since NO customer invoices were “cleaned-up” in the process, evidence is pointing towards inventory. So many customer invoices with so much data on each invoice is what really slows us down – reports are slow, searching through customer cards is slow, etc. – really wanted to remove lots of those customer invoices.
Do you have any ideas about how inventory can prevent data from being purged, or what can be done about this?
Thanks,
Deanna and Tami
Let’s start with the one vendor (fuel company) and the 1 vendor bill that was not removed. You posted that the bill contained an inventory item. What are the inventory levels (units) associated with that item? Are they positive, negative, or zero?
Data isn’t cleaned up if it is linked to an open transaction, and there’s a link in your inventory that is open.
How many inventory items do you have? Is your inventory accurate or do you have a lot of negative balances? Do you use estimates? Provide a few more details about your inventory and we’ll get it resolved.