Is it Door # 1 - Application, Door # 2 - Installation or Door # 3 - Data?
Is it Door Number 1, Door Number 2 or Door Number 3?
We have been focusing on differential diagnosis, and in our last article within this series we placed an emphasis on differentiating Application and Installation issues, so today we will look at the remaining component, the Data File. But first, what about the answer to that question I posed at the end of the last article? Well let me refresh you on that Case Study: “A user complains that her QuickBooks 2012 Pro will not switch from multi-user mode to single-user mode. She also tells you that the other two people in the office have no problems switching QuickBooks to single-user mode when they need to. You further learn that all 3 copies were installed from a retail CD-version purchased at the local office supply. Based solely on the information you have from this description, what is your first impression?”
- This is probably a software bug associated with this version of QuickBooks which is not necessarily found in every copy of the software sold in retail stores.
- This is a software issue probably resulting from an improper or incomplete installation of QuickBooks on this user’s machine.
- This is typically a Company (data) file issue associated with a copy of the data-file on this specific user’s computer.
- This is almost always a ‘user error’; because the user simply doesn’t know how to switch to single-user mode from multi-user mode.
- This is most likely a networking issue associated with this user not being a Windows Administrator for the Server where the Company file is hosted.
I was surprised that no one submitted an answer via comments to the last article. Either nobody is reading these articles, or I have you stumped. Well just in case you are reading, let’s rule out the obvious ‘wrong’ answers.
As it regards answer # 1 - Almost all software produced for retail (box) sales is produced from the exact same factory, and uses the exact same production process; therefore a bug present in one copy will almost certainly be present in the next copy. Furthermore if this was a software bug, it would be widely reported and common knowledge to the ProAdvisor community on any software that was 2 years old. So Answer 1 isn’t correct.
With respect to answer # 3 – There is no unique copy of the data-file on this user’s computer, it is a ‘shared’ data file and other users are not having this problem. So Answer 3 isn’t correct.
Concerning answer # 4 – It would be odd that someone couldn’t simply select the option to ‘Switch to Single User Mode’ (or Multi-User Mode, for that matter). Anyway it is simple enough to determine if the user knows how. Answer 4 is most likely NOT correct.
Answer # 5 isn’t correct either. Every user of QuickBooks must have full rights/permissions for accessing the QuickBooks file on the host computer (server or other host). Networking issues that would prevent a user from switching to single user mode would also prevent the user from using QuickBooks in multi-user mode.
The correct answer is Answer # 2. The most probable cause is that there is an improper installation of QuickBooks on the user’s computer. The result is that either a portion of, or critical component(s) of, the software were not been installed properly and are affecting the software’s performance. (Remember our Data Detective slogan….when you rule out every other possibility, the one that remains, no matter how improbable must be the answer.)
To expand on the answer slightly, if the ‘other computers’ are not having the same problem, and yet the software was installed from the same disk, the disk should not be flawed, nor the application itself corrupted. If the same error results regardless of company file in use, then the ‘data file’ is NOT the cause of the problem. So if neither the software nor the data-file is the source of the problem, then the installation must be the source.
So this takes us to the ‘data file’ as a potential cause of performance problems. There are at least three things to remember regarding the data file. There is a file, the file has structure, and the file contains data.
Technically database corruption is any damage to a database that upsets its integrity and/or impairs its functionality; this includes errors or corruption in the data storage structure used by the database. Like any ‘file of data’ the file can become corrupted from any number of causes such as portions being written to ‘bad sectors’ of a hard drive resulting in data loss or a loss of data (structural) integrity. The file structure can lose integrity, a flaw in the database design, or corruption of structural elements of the database, can cause either portions of the data to become inaccessible, or the file to become unmountable. The data itself can become corrupted and such corruption maybe technical or accounting-based in the case of QuickBooks.
While sometimes being clearly evident, database corruption can be unseen, lurking in the shadows even while you are working in QuickBooks. Database corruption may occur on a transient basis resulting only when the data corruption exists in the cached copy of the data resident in RAM memory. In other cases database corruption maybe perpetual, resulting when corrupted data has been written back to the database on the hard drive.
While it is possible that QuickBooks may not evidence some types of data corruption until a specific piece of data is queried, more often than not, some symptoms of corruption will be present. File faults, error messages, irregular appearing data such as lists, out-of-balance transactions or reports, and many other irregularities can be indicative of data damage. Even slow performance, especially when it comes on suddenly, can be a sign of data corruption.
So how can we differentiate data damage from the other causes of performance problems? Well, systematically of course. We run ‘tests’ and use measures of ‘good or bad’ condition. The F2 Product Information window is one source of those measures. File Size, Total Transactions, Total Targets and Total Links are all valued measures of the health of your QuickBooks file. We have previously discussed how QuickBooks performance diminishes as file size approaches the Cache limitations for QuickBooks. List sizes are another; it is know that QuickBooks will perform less efficiently as any list grows closer to the limitation of that list. The Product Information window also contains a measure of DB File Fragments. By definition file fragmentation is a form of data corruption. I have written on fragmentation previously, so I will not take time to discuss it here, but excessive fragmentation can dramatically affect file performance.
Just as Gregory House, MD uses the vital signs of a patient to begin to formulate his diagnosis, we use the F2 Product Information to begin to formulate our diagnosis concerning the health of the QuickBooks data file. But Gregory House doesn’t stop with the vital signs, if you have ever watched that television show, you know he is ‘always’ ordering tests to differentiate one potential diagnosis from another. In QuickBooks we have a few tests that we can use.
The first test isn’t what most of you are thinking; it is NOT the Verify Data utility. No the first test is the ‘sample file test’. If you can open a sample file, where the sample file is on the exact same computer as your own company file, and you do not experience any of the difficulties or performance problems associated with your own company file, you have come a long way in finalizing your determination that ‘your data file’ is the cause of your problems. If on the other hand, you experience the exact same problems within a sample QuickBooks files that you saw in your own company file, it is likely that your own company file is NOT the source of your problem(s). In order to test this accurately, you must attempt to duplicate (exactly) the same task, including identical steps that produced the problem in your company file. Obviously if the company file isn’t the cause, then you are back to differentiating the software, from the installation and the environment of your system.
Now if your company file seems to be the problem, because you cannot ‘duplicate’ the problem in any sample file, but the problem reoccurs and/or persists back in your own company file, then additional tests are warranted for your company file. The one of these is the automated check of your data which QuickBooks calls the Verify Data Utility.
The QuickBooks Verify Data utility is a two-fold process. The first step is a validation of the integrity of the database, without consideration of the accuracy of the data elements themselves. Validation causes the rows of each table to be read and all columns to be evaluated; indexes are scanned and checks are performed to verify that the rows referenced in the index exist within the tables. Checks on the validity of individual index pages are also performed and the number of rows in the table must conform to the number of entries in the index. If you examine the QBWin.log file, you will notice that the ‘Database Validation’ occurs very early on in the Verify process and usually takes just seconds to complete, either success or failure are reported.
The second step is a verification of the accuracy of the actual data elements by performing individual lookups for the rows of data in conformity with what one might consider the internal rules of the database and the application. During the process each table of the database is scanned and each record in every index, and key defined within any table, is verified. For each entry that is in an index, this process checks the database page that the entry starts on and checks that an entry for the row exists in the proper index, it also verifies all pages used by each entry. Obviously this process takes much longer than the validation, especially on very large databases such as those running QuickBooks Enterprise Solutions. The QBWin.log file will report various phases of this process starting and ending, as well as posting any ‘negative results’, yielding a wealth of results by which you diagnosis may proceed.
Obviously you must obtain the ‘chart’ produced by the test; a test is absolutely no good if it doesn’t produce visible results. The chart for the Verify Data utility is contained in the file we have referred to as the QBWin.log. Now I have also written extensively about the QBWin.log file, including the very first segment of this series, but in our next edition of this series we will look at some QBWin.log data that may aid you in your diagnosis. We will also begin to summarize the various ‘diagnostics’ tests and procedures so as to blend them into a comprehensive picture and solution set. After all diagnosis is simply the first step at reaching a cure.