Sometimes ‘content’ just gets shuffled around here, something comes up and seems more important, so an intended article gets set aside, then more stuff piles up on top until finally you must dig out your original editorial intentions. I came to the realization that my monthly series on ‘Differential Diagnosis Revisited’ that I started with Part 1 in June and updated with Part 2 and Part 3 in July was about to miss an August installment if I didn’t get it slipped into place right now. So, for those of you that have been holding your breath in anticipation, and are now blue in the face, just go ahead and slowly breath out and then take a whiff of this.
Part 1 of this series discussed the concepts of differential diagnosis and looked at how those principles used in 'medicine’ could be used in diagnosing QuickBooks irregularities. We also looked at the fundamentals of ‘Chief Complaint’ as being the starting point for patient assessment, then applied that to a QuickBooks situation of“QuickBooks just crashed.”
Differential Diagnosis Re-visited Part 2 Figure 1
In Part 2 we set down our stethoscopes and EKG monitors used in our diagnostics and considered some of the ‘data detective’ requirements when it comes to QuickBooks problems that necessitate that we treat such a ‘Chief Complaint’ as a ‘Crime Scene’ as well as a patient complaint. The importance of preserving the ‘status quo’ by replicating a copy of the QuickBooks ‘file directory’ for testing purposes was emphasized. In that way, we can go about our various detecting and diagnostic processes without worrying about the actual file until we have things sleuthed out.
In Part 3 we continued our sleuthing by looking at the ‘Game of Clue’ to help us understand how the various players fit into the puzzle. While the game had suspects like Professor Plum and Mrs. Peacock, the suspects in terms of QuickBooks are the QuickBooks Application, the QuickBooks Server, the QuickBooks Workstation(s), the Network Components, the QuickBooks Company (Data) file and the various QuickBooks Users (the ‘human factor’).
While in many cases this last suspect is the one ‘who dun it,’ we shouldn’t rule out the others. So, last time we focused on the first three in the list: the application, the server and the workstation(s). In this episode, we will more closely examine the remaining three: the network, the company file and the user(s).
The Network
Network failures (loss of connectivity from numerous causes including power loss or hardware failure) may be the culprit we have been sleuthing for. The QuickBooks applications’ connectivity to the Company (data) file is extremely sensitive and any ‘glitch’ that results in a transient loss of that connectivity almost always results in data-file corruption.
Loose network cable connections; frayed network wiring; weak or failing routers, switches and hubs; and wireless networks all pose substantial risk to your QuickBooks data-file. Networks running QuickBooks should be operating at 100 MBPS at the very minimum (and be prepared for everyone to be screaming how slow QuickBooks is if you attempt a 100 MBPS network).
For all practical purposes a 1 GBPS (1000 MBPS) network running in ‘full-duplex’ mode should be considered ‘the standard.’ As costs come down there will be greater use of ‘fiber’ based networks over copper, this will result in even faster connections of 5, 10 or 15 GBPS (and that will greatly accelerate the client-server networking model QuickBooks uses for multi-user configurations.)
It’s not enough to assume that your network is running at the ‘rated speed,’ you need to check network speed on a regular basis. We recommend the use of TotuSoft Simple Software Solutions’ LAN Speed Test software to determine ‘the pulse of your network.’ LAN Speed Test builds a file in memory, then transfers it without effects of windows file caching, all the while keeping track of the time required, from which it computes your network speed.
Since you are more than likely using a server on your QuickBooks network you should also use the TotuSoft LST Server product which allows testing between client workstations and the server without hard drive limitations; producing a more accurate reflection of Network performance.
By the way, if you don’t want to shell out the cash for their professional products, TotuSoft also offers a free utility called LAN Speed Test Lite, which will also measure file transfer speed across your Network.
Proper performance testing can determine bottlenecks such as an improperly configured LAN card, a bad or partially broken network cable, a failing switch port, or router going bad.
All of these can significantly impact your QuickBooks operating performance and result in that all too common Chief Complaint of, ‘my QuickBooks is running really slow.’
Wireless networks are notorious for diminished or lost signal strength and bandwidth resulting in transient disconnections; each connection loss produces fragmentation or corruption in the Company-file if QuickBooks is running across the wireless network. As such, QuickBooks should ‘never’ be run on a wireless network, and Intuit clearly states in the ‘systems requirements’ for each version of QuickBooks that they do not support QuickBooks on any wireless network.
Remember that all ‘wired network’ components should be protected with an Uninterruptible Power Supply. It does no good for your workstations and server to be protected by a UPS, if a loss in power still results in an abrupt connection loss between QuickBooks and the QuickBooks Database Server hosting the Company file; due to power-loss affecting an unprotected network switch, hub or router.
The QuickBooks Company (Data) File
I probably have written more about the QuickBooks data file than any person alive on the planet. There is absolutely no way we could ever cover every possible type, form or cause of company file corruption that might result in ‘QuickBooks crashing’ or even ‘QuickBooks running slow.’
To summarize, many years ago Intuit chose to divorce their relationship to C-index supporting the C++ programming language upon which QuickBooks was based and to make use of Sybase database technology for Pro, Premier and Enterprise. The defined storage structure consists of a framework where data pages provide the lowest level of granularity while employing a higher level structure based on blocks of pages within the database. Within the database pages, data is stored in a defined format depending on the type of page (data, index or other page types) and database schema.
Database corruption can occur when the formatting of one or more pages becomes damaged, rendering the information on that page, and perhaps many other pages, inaccessible to the application. Other forms of corruption arise when the actual database structure is adversely affected.
On occasion, application programming errors may result in algorithm failures and impact database logic resulting in data or database corruption.
While it may seem odd to consider something arising out of the way that data is stored on a computer hard drive as ‘file corruption,’ the reality is that fragmentation is the most common form of data corruption impacting databases in general, and QuickBooks specifically.
Fragmentation, by definition, is any condition causing greater than the optimal amount of Disk Input/Output (Disc I/O) to be performed in accessing data or causing disk I/Os to take longer than they optimally should. It’s almost identical in definition to ‘corruption’ itself.
There are two kinds of ‘fragmentation’ that can affect your QuickBooks data, disk fragmentation and database fragmentation. The reason that fragmentation is the most common form of corruption is that it is part of the ‘normal’ windows file allocation schema of your hard drive.
Disk fragmentation occurs because of the way the Windows Operating System stores files on any hard drive. Whenever a file is written or rewritten, Windows attempts to store that file in one long string without any spaces.
When the hard drive is new, with lots of free space, it is a relatively easy function for Windows to perform. But with the addition of more and more data to the drive, and modifications and deletions of data, spaces and gaps begin to appear on the hard drive.
When Windows can no longer find one contagious area of the disk to write the entire file, it starts writing the file wherever it can by breaking up the data into smaller pieces and storing it all over the hard drive wherever it can find space.
The most noted side effect of this fragmentation is performance degradation caused by the disk I/Os taking longer than they optimally would. Seek, read and write functions are all prolonged as the disk drive head may be required to move to multiple areas to service the data.
Because some QuickBooks databases are extremely large, and complex, significant disk fragmentation can have major effects upon the performance of the QuickBooks program as well as integrity of QB Company-file data.
The second kind of fragmentation affecting QuickBooks is database fragmentation. Because of the QuickBooks data structure, tables, and indexes that are being used, fragmentation is another form of data-file corruption that can cause performance issues as well as using unnecessary space in the database.
Database fragmentation is similar to disk fragmentation in that the data is stored in various places in the database file and not necessarily in sequential order or next to similar data within the database. This often occurs from non-sequential keys and the constant inserting, updating and deleting of data causes the data to become fragmented.
Optimal performance occurs when data pages are as contiguous as possible within the database, and the data pages are packed as fully as possible. Fragmentation can manifest itself in many ways in the database including messy page chains, poor page utilization, extent fragmentation, and row fragmentation. The second kind of fragmentation affecting QuickBooks is database fragmentation.
Messy Page Chain fragmentation occurs when the data pages within the page chain of a table or index are not contiguous and in sequential order within the database. Messy page chains cause excessive disk seeking during full table or index scans and queries. When data pages within the page chain are not physically contiguous (next to each other) reading the pages in the page chain involves seeking the disk heads to each new page in the chain— this can be a very time-consuming process.
Of course, an even worse form of corruption occurs in the form of ‘broken page chains,’ one of the most significant errors currently being experienced by some QuickBooks Desktop users involves various forms of ‘target-chaining errors’ that result from similar mechanisms within the database.
Access errors occur when the application is trying to access a table, but the page that was read had the object ID of a different object in its page header. There can be many mechanisms behind this type of corruption. For example, the page chain of the specific table of data, or one of its indexes, can be corrupt and point to a page belonging to another object in some other table.
Another cause of access error occurs when a page belonging to a specific table is allocated to a specific object and remains part of the appropriate page chain, but the page has been erroneously overwritten with data from another object. If the data on the desired page has been corrupted in some way, even though it may still contain all or part of the original data, the page may appear as an invalid object.
At times the server may attempt to access a database page not found in the cache and then reads the page from disk; this can result in errors if the page data for a specific database page was written to the wrong location in the database or if the application attempts to read the page from the wrong location.
Some errors occur when the target page of data has been overwritten with the contents of another page, such as a source page. In these cases, the original contents of the target page have usually been overwritten and are lost unless some intervening backup copy of the data exists absent of the corruption. Copies of the missing or overwritten data may be available from the *QBW.TLG (transaction log file) which can be identified and applied to a copy of the *QBW file as long as the *QBW and *QBW.TLG files match.
There are several potential causes behind errors resulting from the inability to find a page on the server. When logical page numbers within a database cannot be found because there is not a corresponding device-related page on the drive, page chains of a particular page in the database may have been linked to an out-of-range or incorrect page number or an index pointed to an incorrect or out-of-range page number.
Sorry for being so long-winded on this 'suspect,' but I could go on and on about the various mechanisms of QuickBooks file corruption. The key point to be taken away from this discussion is that the ‘Company file’ is always a likely suspect in any QuickBooks issue.
The QuickBooks User
What can we say. Just as ‘human error’ is almost always an underlying aspect of any ‘aircraft accident,’ so too can it be said for QuickBooks issues. If the human condition does not cause, then it likely contributes to, the underlying problem(s).
I don’t know how many times I have users tell me, “well QuickBooks stopped responding” so I just went ahead and “shut it down with CTRL-ALT-DELETE” and now, “it won’t open at all.”
That’s somewhat akin to saying, “my car ran out of gas, so I just left it in the middle of the highway, and a Semi hauled with gasoline crashed into it and my car has burned up in the explosion resulting from the crash.” Only to be followed by the exclamation, “Hey, it’s not my fault that the car ran out of gas in the middle of the highway.”
There is just no arguing with some people, like the guy with the baseball bat.
Next time, in Part 5 of this series, we will begin our use of flow-charts to help us differentiate the diagnosis of each of these components, as well as develop a testing and treatment plan for our QuickBooks Chief Complaint of "QuickBooks just crashed!"