When you create a company in QuickBooks there will be several files created. One has a file type of “QBW” – that is your actual company data file. Another that is created has a file type of “TLG”, and that is the source of some confusion as well as some misunderstandings. Let’s review a few facts about this file.
What the TLG File Contains
As you use your QuickBooks company file it is being updated with the transactions that you enter. In addition to updating the company file, QuickBooks will save information about the transactions in the TLG or Transaction Log file. QuickBooks is tracking all of the changes that you make to your company file. The TLG file continues to grow, and it isn’t unusual to see the TLG file become bigger than the QBW file.
I’m told by some people that as the TLG file grows you may see some decrease in system performance, at least when the file gets very large. I’ve not been able to confirm that myself. The increased file size certainly can impact performance and possibly cost of backup programs and procedures.
As a side note, you may also see a file with an ND file type – this is the NetConnect file, containing information that relates to multi user access to your company file. You should not erase it.
What Use is the TLG File?
In your normal day-to-day operation of QuickBooks, the TLG file is of no use to you. You don’t have a way to look at it, your use of QuickBooks won’t be affected by its presence. So what is its value to you?
The value of the TLG file is for data restoration. If your company file is erased or damaged, the TLG file can be used in conjunction with your most recent backup to restore transactions that were entered since that backup. Let me give you a simplified example.
- You have an inventory item that has an on-hand quantity of 10.
- You make a backup copy of your company file (more on this later).
- You receive 20 more of that item.
- For some reason your company file is damaged, but you still have the current TLG file and your backup file.
- You restore the backup, now you are back to an on-hand quantity of 10.
- The transaction log is applied to your restored backup, it gets you back to 20 on-hand.
Sounds simple? Unfortunately it isn’t. You cannot apply the transaction log, only certain businesses can.
Who Can Restore a TLG File?
There is no user-accessible utility program that will let you take a backup file and update it with the transaction log. Some options that you have are:
- QuickBooks Data Services (Intuit QuickBooks Support): Their web site is a bit vague, it is not clear if these fees apply to applying a transaction log to a backup. Their data recovery service comes in two flavors, “standard” at $250 with your project being added to the queue (I’ve heard it can be multiple days or more) and “express” at $750 where you go to the top of the queue. There is a nonrefundable analysis fee.
- AccountingUsers, Inc: They advertise a standard service ($275 for two business days) and expedited service ($450 overnight). No fee if they are not successful. http://quickbooksusers.com/datarepair.htm
- QB or not QB: At this time I’m not sure what their fee is, most likely it will be variable depending on the size of your file and how quickly you want it turned around. http://www.qbornotqb.com/
Each of these services have a cost – a fee that you will have to pay, and a turnaround time during which you cannot use your QuickBooks company file to add or change ANY information.
Note that I have not used any of these services for this kind of recovery. I’ve spoken with the people at AccountingUsers Inc and QB or Not QB on multiple occasions, and they sound like reasonable people with the appropriate technical knowledge, but I haven’t tested the services.
Why Not Just Delete It?
OK, we’ve established that the file gets large and takes up space (particularly if you are copying backups and this file to a CD or other media), that it might slow down performance as it gets larger, and that you have to pay money to be able to use it to recover data. Why bother with it? Can’t I just delete it?
If you have been reading my blog for long you will know that I am a bit hyper about data security and backups. So, do we need the file? On one hand, if you have a good backup plan (see Backing Up QuickBooks Data for example), you are making regular backups in several ways, and if a disaster occurs you can restore your backups with a minimum of fuss. No need for the TLG file?
Well, I don’t agree with that. I believe that you have to take advantage of every opportunity for data security that you possibly can. That means that you should be using the QuickBooks backup feature, and including the TLG file in your set of files that are backed up daily (or at least very often). It adds one more level of safety to your backup plan, in case you run into an odd situation. Sometimes, for example, you can have damaged data in your QBW file that just cannot be fixed. The damage may not be noticeable right away, so your daily backups might also be corrupted. A good QBB backup and matching TLG file may be what you need to get back to the point before the damage occurred, and then move forward to your current state.
Note that if you delete the TLG file, QuickBooks will just start another, but it will have limited use as it is incomplete.
Managing QuickBooks Backups and the TLG File
The general concept for managing backups and TLG files is that when you make a backup using the QuickBooks backup feature (from the File menu), QuickBooks will create a file with a QBB file type and clear out the TLG file. The backup has all the transactions saved, so you don’t need that transaction log. From that point on the TLG file will grow again, holding all transactions since that backup.
However, not all backup files are equal. QuickBooks will only delete the TLG file if you make a manual backup with the verify option on.
If you do not select the complete verification, the TLG file is not cleared and your QBB file cannot be used with that TLG file. In addition, this must be a manual backup, selecting backup from the File menu. A scheduled backup will not perform this function, even if complete verification is selected. QuickBooks Online Backups, or backups that you make yourself with your own backup programs, also won’t work.
The TLG file and your company file must match. You cannot use it, for example, to “merge” transactions from another computer (such as your home laptop, to merge to your office workstation). If you copy a TLG from your laptop back to your office computer, QuickBooks will see that they don’t match and will “reset” the TLG file, lose all saved transactions in the log.
I should note also that making a portable company file has no affect on your TLG file – BUT if you RESTORE a portable company file, it won’t match the TLG file and the TLG file will be reset.
Here is a company file (fairly small) and a TLG file before making the backup:
Here are the files after a manual backup with the verify option on.
So, my recommendations are:
- DO NOT delete the TLG file if at all possible.
- PERIODICALLY run a manual QuickBooks backup and save the QBB file
- INCLUDE the TLG file in your daily backup routine.
Very interesting about the automatic scheduled backup not doing the same thing as the manual one, Charlie. Why is that?
Lynda, “why” is always an interesting question, and when we talk about Intuit and QuickBooks, the answer very often is simply “because that is the way they did it”. I could speculate, but I can’t really say why in this case. They may have a reason, it may just be because that is the way they implemented it. We may never know…
Charlie,
You mentioned an interesting concept.
Just one question on your last example. Your example shows a .qbw file being bigger after the backup was made. Is it because subsequent transactions have been entered just prior to the backup creation date? I think creating a backup should NOT increase the .qbw file size. Is my understanding correct? Thank you.
VS Lingam: There are times when a backup with full verification WILL make the file slightly larger – the verification process may have adjusted some information in the file to correct problems that were resolved. I’ve not seen anything other than a small incremental increase myself, but I’ve not monitored that closely. The sample file I used in this test was somewhat small, I had planned on using a very large file but I ran into some issues with it not passing verification so I went ahead and used this one.
So,if I’m understanding this correctly, every time you back-up the QB file selecting the complete verification option, the .tlg file is updated to match the new back-up? If the .tlg has been deleted and QuickBooks creates another .tlg it will be incomplete, only including the most recent transactions since the last complete verification back-up?
Jan, when you do the manual backup with complete verification, the transaction log is essentially erased and recreated, empty. ANy new transactions that are created are added from that point on. So that the TLG file has every transaction since the backup was made.
That lets the support people recreate a CURRENT file by taking the backup and matching TLG file, which has all transactions since the backup was made.
If you delete the TLG file yourself, QuickBooks will create a new one, but it will only have the transactions from that time, not all since the backup. Therefore it is, essentially, useless to do anything.
Make a manual backup once a day and ignore the TLG file altogether.
Ron, for some situations, that may not be practical. Only one user can be logged in, and for some large files this (with full verification) can take a very long time.
I do believe that backups should be done on a very regular basis. And I personally think that the TLG file is of limited use, it is really only a last ditch disaster recovery issue. But, backups are inconvenient, and when something is inconvenient people tend to not do them. For some people, a full blown manual verified QuickBooks backup procedure is too painful to do on a daily basis…
It is ridiculous that QuickBooks’ AutoBackup feature, which I run every time the QuickBooks file is closed, does not accomplish the same things as a full manual backup.
It should be trivial in a multi-user environment to program the necessary backup when the last user logs out, timing out inactive users, so that such a backup can be performed overnight, which should address most cases.
The point is, computer programs are supposed to do be able to do things for us automatically, not cause us “inconvenience and pain on a daily basis.”
There appears to be a regrettable lack of user-focused leadership in the definition of functional specifications at Intuit.
Ron, I don’t know enough about their internal software setup to know if it is trivial – I suspect that it isn’t in their case – but I’ll agree that it is something that they should address if possible.
I upgraded to Pro 2009 from Pro 2005 a couple days ago. My .qbw file size is 196,528 KB. My Pro 2005 .QBB file size is 51,968 and the Pro 2009 .QBB file size is 142,344. It takes 3-4 times longer to backup. I’m used to backing up on my hard drive, copying to another hard drive and copying to a flash drive. All this used to take a few minutes. It now consumes almost 1/2 hour. I don’t see significant feature improvements in the upgrade. Can I go backwords to Pro 2005 or am I stuck?
Peter, starting with the 2006 upgrade, the file size increased significantly. They changed to a newer, faster and more secure database. You don’t have good options as far as “downgrading” your file, you can only move your “list” items (not transactions), or purchase some expensive tools. Even then you will lose some info. So, not easy to do.
Lots of good things in more current copies of Pro, but many businesses don’t use or need the new features or options…
Thanks Charlie
For a small business like mine, might you recommend the online version? Or… would you recommend I compact my file or fire up a new company file to gain some speed.
Again, thanks!
QuickBooks Online is a very different product than the QuickBooks desktop product you are using. I don’t work with it in detail, but lots of things are different, and the pricing of this is very different as well. In a very general sense I usually don’t recommend going to that unless there is a compelling need to be able to access the data from different offices. And even then not necessarily.
Starting a new file, or shrinking the one you have, can shrink things but you won’t get back to the size you had before, possibly. It is a lot of work. If you are taking all that much time in doing backups, I’m wondering how you are doing it and what kind of computer you are working with. That seems like it is taking a longer time than necessary, but it is hard for me to say without having hands on your system.
Anybody know how to import a TLG file into Excel or any other readable format?
David, there really isn’t a good way. None of the programming toolkits can use it. There are a few people who may have figured out how to decrypt it, but they keep their knowledge to themselves.
We have been having a strange problem for the last couple of weeks since upgrading from QBE10 to QBE11. The TLG file grows by about 600mb per day. The QBW file is about 400mb. This would quickly fill up the backup space (also when it reaches several GB it might slow things down). My workaround has been to delete the TLG file after backup every night. Backups are not through QB, although we occasionally do that. As noted above, auto QB backup doesn’t shrink the TLG file.
It’s worth mentioning that we also use an add-in called “All Orders” by Number Cruncher, and we did a minor upgrade to it at the same time (same major version). That could be the source of the problem.
Thanks,
M.H.
Mike, the TLG file will grow as transactions are added. If All Orders is adding a higher volume of transactions (which it might, but I don’t work with that very much) then your TLG file would grow faster.
Deleting the TLG file as you do will work, but keep in mind that you are destroying one portion of your security blanket in case things blow up. On the other hand, with a good backup procedure in place, I’ve never had a client who needed to use the TLG file. I can’t say if your backup procedure is good or not – must making a system backup every night is NOT by itself a complete and adequate backup process.
Thanks for replying. I won’t bore you with details of our backup plan, but we do have rotating backup media, the newest of which is offsite. We also nightly sync with another server at another site. I’m pretty sure we would be OK without the TLG, but we never had this problem before. Of course there is a copy of the TLG with every backup, before it is deleted.
We actually did have a crash once (2007, I think) and had to do a recovery from backups. And that was with a RAID disk. (The controller failed and scrambled all of the data, let that be a lesson to those who rely too much on RAID).
I have also logged a question about this at Number cruncher.
Thanks,
M.H.
Mike: A side note – if you are deleting the TLG each day after backup, most likely the TLG files aren’t going to be of any real value. The design assumes that one TLG file will have all of the transactions since the last QB backup. In your case, that isn’t true. I’m not sure that Intuit has a way to restore multiple separate TLG files in sequence like that.
You may have a point in that we could be backing-up a corrupted QBW file and not know it for a while, but I don’t know what else to do at this point. We don’t do anywhere near 600mb of updates per day. Before the upgrade the file would grow by a few mb per day, which is a believable rate for us.
I could try to run “verify data” periodically but the wee hours is the only time the data file is available single-user.
Thanks