The Mysterious QBW.TLG file

by

I have written several articles recently under the banner, "Fracturing QuickBooks Desktop." A couple of those covered the mysteries of passwords, encryption and decryption.

Recently, I wrote an article giving you a peak into the steps that take place when you upgrade a Company file from one version of QuickBooks to another.

Here, we want to cover some more secrets about the inner workings of QuickBooks by looking at the mysterious QBW.TLG file. You might say I'm giving away many of my secrets related to the QuickBooks Desktop and the associated Database files. 

The *.QBW.TLG file is the Sybase transaction log file, it records all of the database server’s activities related to your Company file, including all reads, writes, edits, inserts and deletes. 

But unlike a lot of other "log" files associated with QuickBooks, this one is not easily translated just by opening it with a text editor.

In order to illustrate this point, I want to spend a moment looking at our old friend, the QBWin.log file. If I open a QBWin.log file using WordPad or Notepad, we get some very useful information. 

Most ProAdvisors have learned how to interpret the information within the QBWin log file, in part because I have spent the last six years writing numerous articles about this log.

It is easy to read the information in a QBWin.log because it's written in plain-English, even if it still seems like Greek to some of us. But this article isn’t about the QBWin.log, it’s about the .TLG (transaction log) file. 

I just wanted to illustrate how these logs differ when you open each of them in a text editor.

Now, if I open a .TLG file in a text editor, it doesn’t look anything like a QBWin.log file. The .TLG file looks like nonsense, because it’s written in some weird language with all these symbols and characters that don’t mean anything.

In fact, if we look closely, it kind of looks a lot like ancient Greek or Hebrew, or perhaps even Egyptian hieroglyphics.

I can’t even find a single word of English in the bunch. I wonder if Siri could translate this if I opened it with my iPhone? I really don’t think so, although my iPhone clearly understands this stuff. 

That’s because the TLG log is written in a modern language, a machine language known as “ASCII,” which stands for American Standard Code for Information Interchange. This code is to computers what Greek was to ancient Greeks.

So, what’s the computer do with this data, and why is it important to QuickBooks?

According to the QuickBooks technical support website’s knowledge based article on the .TLG file, “If QuickBooks loses connection to the company file abruptly for any reason, the TLG file automatically corrects 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.”

While that maybe so, let me say that of all the people I've talked to over the years who have sent files into Intuit’s QuickBooks Data Services hardly any have ever been asked to submit a .TLG file for file recovery purposes.

Let’s begin our deep dive into the .TLG file by looking more closely at the first reason Intuit gives for the importance of the .TLG file – the part about the .TLG file auto-correcting the data file.

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 in the file, this is known as a clean shutdown.

Each time a user starts the database, the database server checks if the last shutdown was clean or the result of a system failure.

Then, 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. It will correct some problems using data from the .TLG file if corruption is detected.

If the database was not shut down properly, the database server is designed 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 internal rollback data.

This rollback 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.

My Insightful Account article, “How QuickBooks Uses Cache Memory,” contains more information on the mechanics of the QuickBooks Database.

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. In addition, the number of pages that must be allocated; some other page management information is also logged.

Now if you were in one of my Super Geek classes at Scaling New Heights or, were reading from my article, “The QuickBooks TLG File Revisited”, I'd explain some things about the size and importance of maintaining the TLG file. I'd also explain how it can be used by File Recovery specialists to repair your data file. I might even talk about errors associated with the TLG file and how you might resolve some of those errors.

But this article is intended to unlock some of the inner mysteries of the .TLG file, so we're breaking out our microscopes and are going to look inside one example of the file.

If we translate the QBW.TLG file with the Sybase SQL Translator we can see the “ASCII” gibberish turns to readable language right before our eyes. Unfortunately, it still doesn’t make much sense when viewed in this manner.

In addition, it really is of no use in terms the of the two main purposes of the file, the automatic repair during a roll-back or a Professional File Repair/Recovery. For that, we need specialized tools that allow us to examine the true inner workings of the .TLG file.

But before we do that, let’s take a moment to look at what Windows can tell us about our ‘example’ .TLG file. When I open the Windows Properties of this file, I see that it's called Processed Stuff.qbw.tlg. This specific .TLG file is only 5MB in size.

Like I said, with the right tools, we actually can open our Processed Stuff.qbw.tlg file. When we do we find that the file looks remarkably like the inner workings of a QuickBooks Company (*.QBW) file. 

There are all kinds of tables containing data, as well as system information and the behind the scenes tables of system and instructional data. If you look closely, you'll likely see some common table names, like the Vendor type table, or the Payroll Item table

Note that the right-hand column Changes displays a count of the number of changes made to each of these tables since the TLG file was initiated. But these common tables are only part of the critical information in the .TLG file.

In the illustration below I have opened up the structure of just one of the tables within the .TLG file, a table called I_GENERAL. Seems kind of interesting to me that at this level of sophistication an application would be reliant upon information in a table called I_General (for Information_General). Obviously programmers do funny things at times.

We now can see the kinds of information being tracked in this table, including the associated key (key_fld) and record number (rec_num) fields that track the points of activity.

But the real data is stored in the data field (data_fld) column. With this understanding of the structure of this one table, let’s examine how the data in this table actually appears.

In the table below, we can see a lot of inserts as well as information as to the ‘where of each insert’, and the data to be inserted.

The data being recorded in the .TLG file, and this table specifically, is not just the same information as was recorded in the actual Company (QBW) file, but it also includes the nature and details of the changes made.

For example, when a single row of data is updated in a QuickBooks Company file table, the following types of records are being written to the transaction log.

When it comes to inserts, one Data insert record is recorded that includes all of the data in the modified row within the active table of the Company file, and One index insert record per index affected by the change is also recorded in the .TLG file. Simultaneously, One page allocation record per new data/index page required is recorded.

When it comes to deletions, one Data delete record, including all of the data in the original row within QuickBooks is recorded in the .TLG. Even though the data is ‘marked deleted’ from the active table record, it is tracked elsewhere, because it's actually inserted into a different table that tracks deleted records. 

If that wasn’t so we would not be able to produce a voided or deleted transaction report. As a companion to that event, one index delete record per index affected by the change is recorded in the corresponding TLG table, and One page de-allocation record per data/index page freed-up is also recorded.

Obviously, there is a lot of data being recorded for each read, write, edit, insert, and delete you undertake within QuickBooks. But what about the actual data that is enumerated within the data field with all the ‘blue links’?

If I open one of the data varbinaries, we are back to mumbo-jumbo, because the data is displayed in HexView.

With the right diagnostics, it is possible for qualified data service professionals to determine the corrupted or missing information within the original source QuickBooks Company (QBW) file. It’s then possible to use the data from the .TLG file to build an ISQL script consisting of one ‘Insert’ statement for every row in a corrupt QuickBooks table, even including data pages that might not be accessible by the QuickBooks database server.

This is, of course, the second purpose of the .TLG file (fulfilled), and illustrates the real value of the *QBW.TLG file. Accordingly, this value emphasizes the importance of maintaining the .TLG file properly.

This means using the internal QuickBooks back-up in full-verification mode as the only way to ensure that the data in the Company file matches perfectly with the data in the .TLG file. It also is the only way to properly reduce the size of the .TLG file.

A client recently told me that Intuit Technical Support advised them to simply ‘delete’ their .TLG file because it had gotten too large, and that QuickBooks would create a new .TLG file. The Intuit tech said that because they were using an off-site back-up for their QuickBooks there would be no corruption in the Company file that the .TLG file could fix. 

I must respectfully disagree; with the right equipment and a matching .TLG file, data service professionals can frequently fix whatever is wrong or missing in a QuickBooks Company (*.QBW) file.

Personally, I never recommend deleting the .TLG file. While it's true that QuickBooks will automatically create a new .TLG file. If your data-file is corrupt in any manner, deleting the .TLG file means that you have just lost one of the most valuable tools available for restoration of your data, because once deleted, the data-history preserved in the .TLG file no longer exists.