TECHNO TOPIC: The QuickBooks QBW.TLG file

by

Most QuickBooks users and even many ProAdvisors have little knowledge about the file named QBW.TLG that is found within in the same file folder in which the regular QuickBooks Company-file (QBW) is located. The QBW.TLG file is a transaction log file, and a product of the underlying Sybase technology, in which the QuickBooks database server tracks changes to the QBW file since the last time a verified backup QBB file was created.

The Purpose of  the TLG File and how it is designed to work

The QuickBooks technical support article on the TLG file states that “if QuickBooks loses connection to the company file abruptly for any reason, the TLG file will automatically ‘correct’ the data file as much as possible.” The same article also indicates that Intuit Technical Support can use the transaction log file, in conjunction with the most recent verified backup, to recover lost or corrupted data up to the most recently saved transactions.  (Note: An example of the ‘playback’ of a TLG file, using a Sybase utility can be seen in this article’s title figure.)

When the QuickBooks database is shut down during normal operation, the database server carries out a checkpoint so that all the information in the database is properly written to the file, this is known as a clean shutdown.

  • A “checkpoint” is a specific point in time when a consistent state of the data on the disk existed, and all the data is held in the Company-file.

Each time a user starts the database, the database server checks if the last shutdown was clean or the result of a system failure. A series of checks and automatic recovery operations are performed by the database server to confirm that the database and transaction log are synchronous and not corrupt, and to correct some problems if corruption is detected.

If the database shuts down improperly, the database server is supposed to take steps to recover data not cleanly written to pages within the database.  In theory all pages (of data) are restored to their state at the most recent checkpoint by copying the checkpoint log pages over the changes made since the checkpoint. Changes made between the checkpoint and the system failure, held in the transaction log (QBW.TLG) file are then written to the database.

Any uncommitted (incomplete) transactions, which might result in data corruption, are supposedly rolled back, using rollback file data.  This roll-back log is maintained within the data-structure specifically for the purpose of canceling changes in the data if a transaction is rolled back or uncommitted when a system failure occurs.

  • The QBI file is a “roll-back” file holding a copy of the transactions held in the cache until they are posted back to the hard drive at the next checkpoint.  This file preserves the memory-resident changes to the data file while QuickBooks has the file open; it is yet another form of redundancy designed to protect the data from accidental loss. 

What information is written to the QBW.TLG file

Every piece of information related to each transaction processed by the QuickBooks application is written to the transaction log to help insure recoverability.  The quantity of data logged for a single transaction will depend on the number of indexes affected, the amount of the data changed, and the number of pages that must be allocated; some other page management information is also logged.  For example, when a single row is updated, the following types of records are written to the transaction log:

  • Data delete record, including all of the data in the original row;
  • Date insert record, including all of the data in the modified row;
  • One index delete record per index affected by the change;
  • One index insert record per index affected by the change;
  • One page allocation record per new data/index page required; and
  • One page de-allocation record per data/index page freed-up.

Size and maintenance of the QBW.TLG file

The TLG file can become extremely large (even larger than the company file) taking up a great deal of hard-disk space. Since the TLG file is one of the files that are backed up when you perform a QuickBooks back-up of your company file, you could experience problems if you are attempting to back-up to a relatively small USB flash-drive. 

The size of the TLG file is manageable because you can perform actions in QuickBooks to reduce the file size by creating a new TLG file during a fully verified QuickBooks back-up, if the back-up is successfully completed. As such, this further emphasizes the need to always perform manual back-ups with Complete Verification of your QuickBooks data; if the data will verify thru the complete verification there is no need to retain the TLG since all data in both the file and back-up has been determined as valid and verified.

Since a manual back-up without verification DOES NOT delete the historical TLG file and start a new one, the historical TLG file remains available to correct any data corruption issues that may exist within the database once detected.

  • Note: If you are using a back-up program that works outside of QuickBooks, such as Windows Backup or the QuickBooks Online Backup Service, there backup types have no effect on the TLG file; only a manual back-up within QuickBooks with Verification results in the deletion of the old TLG file, and creation of a new TLG file.

Maintaining the Integrity of the QBW and TLG files

Identifiers associated with the database checkpoint within the QBW and TLG file match them together. This integrity must be maintained in order to insure the TLG file contains the data needed to rebuild the data-file in the event of corruption. 

Both files should always be maintained in the same working directory; when a QBW file is present within a directory without its companion TLG file a new TLG file will be created in the directory where the QBW file is resident. Even if the QBW file is copied back to the old directory, the prior TLG file will no longer match-up to the QBW file and it will be deleted and a new TLG file created in the directory.  Obviously a ‘new’ TLG file will not contain the necessary historical information to rebuild a file if needed.  As such, whenever the QBW file must be copied or moved to a new location, the companion TLG file should always be simultaneously moved with it. 

I recommend that the entire directory containing the QBW file should always be copied and moved as a whole rather than its individual components.

Preserving verses Deleting the TLG File

Deleting the TLG file is NOT recommended; while QuickBooks automatically creates a new TLG file and permits you to go on working as usual, if your data-file is corrupt in any manner, you have just lost one of the most valuable tools available for restoration since the data-history once preserved in the TLG file no longer exists.

If the size of the TLG file is creating problems, either run a Manual Backup with Complete Verification, or better yet, get a bigger hard-drive.  Chances are if your QuickBooks files (the QBW and TLG) file occupy so much of your disk space as to warrant your considering deleting the TLG file manually, you probably are trying to run QuickBooks on too small a computer hard-drive in the first place.

Errors Associated with the TLG File

There are several errors you can experience in QuickBooks associated with the TLG file. For example, if the TLG file is damaged QuickBooks may display a warning message identifying error -6000, -301. An error -6000, -107 may result from disassociation between the TLG file and its companion Company (QBW) file. Other errors associated with a mismatch between the TLG file and the Company (QBW) file may include error -6190, -816 and error -6190, -82.

Resolving TLG File-related Errors

Apparently some QuickBooks Users and ProAdvisors believe that the most simply way of resolving errors associated with the *TLG file is to just delete it; however, it is never a good idea to delete this file because if the errors you are experiencing actually stem from, or are complicated by, other data corruption issues within your QuickBooks Company (QBW) file, deleting the file may preclude the ability to resolve such corruption using the TLG as the transaction source from which to make necessary repairs.

A better method of dealing with issues that may be associated with TLG related file errors is to rename the TLG file to something like Company file.QBW.TLG.Old; this will preserve the data within the TLG file for potential future use. (In some cases multiple TLG files can be played back and assembled together to recreate all QBW data, if the sum of the files contain the entire history of the QBW file.)

After the above change (of adding 'old') to the previous TLG file, the database server will not be able to identify any associated TLG file, so QuickBooks will immediately create a new TLG file when the Company (QBW) file is opened.  This new transaction log will begin as of the last checkpoint in the database.

  • Intuit recommends that users also attempt to resolve these issues with the ‘QuickBooks File Doctor’. Unfortunately I have found that the QB File Doctor is rarely able to resolve problems of this nature.