Have you ever launched QuickBooks only to see the warning displayed above? In technical terms this results when your company file 'will not mount', it can not be read by the QuickBooks Database Server due to data corruption.
Despite the fact that QuickBooks is a relatively stable program, the database is subject to corruption resulting from the causes we will address within this article. Database corruption is any damage to a database that upsets its integrity or impairs its functionality; it also incorporates errors in the data storage structure used by the database. Corruption does not limit itself to just fields, rows, columns or even individual tables within the database, the corruption may exist across a broad expanse of the data.
While database corruption can sometimes be evident, it can at times be the unseen enemy of your Company-file, lurking in the shadows when you are working in QuickBooks. It is probably the most common cause of QuickBooks data loss, and despite Intuit’s efforts to improve the stability and redundancy of QuickBooks, it appears to be getting more prevalent as evidenced by QuickBooks forum users posting questions directly related to data corruption.
QuickBooks makes use of Sybase database technology which has a defined storage structure consisting of data pages at the lowest level of granularity and employing higher level structures 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). 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.
Causes of QuickBooks Database Corruption
There are many ways a database, including QuickBooks, can become corrupted. Some causes are user-related, and many others are not. For example, when a User switches-off their PC when QuickBooks is open, the result is database corruption; the extent of this corruption can range from minimal to catastrophic. Redundancy and self-correction procedures built into Sybase technology, and used in QuickBooks, strive to keep corruption to a minimum from such events.
Typical hardware or environmental causes of database corruption include:
- Power problems (black-out power loss, brown-out power dip, or power surge);
- Lightning;
- PC crashes or freezes while QuickBooks is open, resulting in improper shutdown;
- Hard drive disk errors;
- Memory (RAM) errors;
- Networking errors (network connection failure or drop-out).
Another common cause of improper shut down is a power loss which affects either a User (client) computer while QuickBooks is open, or a Server Computer which encounters power loss while the QuickBooks database is running. Similarly lightning and power-surges may also result in improper shut-down of QuickBooks. For this reason, every computer running QuickBooks, be they workstations or servers, as well as all inter-connecting network hardware (routers, switches, and hubs) should be protected using an adequately sized Uninterruptable Power Supply (UPS). While surge-protectors are adequate for monitors and printers, they do not meet the power protection requirements for other vital equipment.
System crashes resulting in an improper shut-down of QuickBooks are becoming less common since later versions of Windows are supposedly more staple thus preventing one crashing or hanging application (“application not responding”) from crashing another application (such as QuickBooks) or forcing the PC to reboot. None the less, a user who reboots the computer because “QuickBooks Is Not Responding”, or some other program has locked up their computer, while QuickBooks is open, almost certainly corrupts the QuickBooks Company-file to some extent.
QuickBooks is a memory (RAM) intensive application. As a general rule, computers running QuickBooks should have as much RAM as possible, with a minimum of 1 to 2 Gigabytes of memory. At times, QuickBooks may display a warning such as “Not enough memory…Exit QuickBooks, quit any other applications, then restart QuickBooks”, if your computer fails to meet the operating memory requirements, due to other applications consuming system resources.
Computer CPU and RAM failures are relatively limited, but do rarely occur. If these solid-state components are operated within the proper temperature, humidity and physical parameters, they will typically run longer than the hardware itself is valued. Most computers today offer built-in mechanisms to detect issues when their electronic circuitry begins to fail, and many offer limited redundancy to permit the hardware to shut down in a manner that precludes data loss or corruption.
While today’s computers are built with safeguards on board, they do not safeguard against abuse by their owners. I can’t recall the number of times I have gone to a new client to find their $4,000 server locked in a closet where the temperature was in excess of 110-degrees because there was absolutely no circulation. In one case the entirety of the client’s QuickBooks database corruptions could be tracked back to hard-drive controller failures resulting at the exact moment that high-temperature alarm errors were being recorded by the operating system. You wouldn’t think about leaving your small child locked in your car on a hot sunny day for even 5 minutes; so why would you leave your Company’s server, containing your entire financial history, locked in a hot closet without air conditioning? The answer is “simply don’t do it” (in either case).
The most common computer component to fail is the hard (disk) drive, because it has the most and fastest moving parts. Hard drives can fail slowly, a few sectors at a time, or can suddenly ‘crash’. Hard drive failures are the most common source of hardware related data corruption over any other computer component. Undetected or inconsistent data errors can significantly compromise the integrity of the QuickBooks database. Since the Hard drive is an integral part of any database, being the primary location of the data’s storage, special consideration must be given to monitoring the performance and integrity of these components.
Modern disk drives use technologies designed to detect errors; these methods such as checksums, cyclic redundancy and other algorithms, can detect more than 99% of unintended modifications of data on the drive. Redundant error correction methods are designed to preclude data corruption by retrying the read/write operation multiple times to see if the data can be stored correctly. Depending on the effectiveness of these methods, many potential long-term complications, while eliminated on an immediate basis, may go unnoticed until more significant drive failures occur. An unmonitored disk might start experiencing failures long before it ‘crashes’, since the relatively minor failures were automatically corrected by the drive’s circuitry and firmware. On the other hand, if a drive is experiencing progressive failure, users start to see errors in data (such as missing information), performance (degradation of responses), application issues (inability to access certain files or tables) and operating system corruptions (Windows shut-downs) gradually becoming more prevalent; and would hopefully recognize these issues as the signs of a potential disaster long before the entire disk suddenly crashes.
Another Hard-drive related issues affecting potential corruption is hard-drive size verses the amount of information stored on the drive. Running QuickBooks on any computer with inadequate hard disk space is just asking for a Company-file corruption disaster. Windows uses swapfile space dynamically, and can fail without warning if disk space is inadequate. As a general rule, QuickBooks should not be run on, and QB Company-files should not be accessed from, any computer disk drive where used space on the drive is more than 60% of the total space on the drive; this includes attempting to store and access any QuickBooks file on portable drives, including USB flash drives. Without a doubt, QuickBooks should NEVER be run on any computer with less than 50MB of free hard drive space.
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, 1 GBPS (1000 MBPS) networks are the standard.
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 does not support QuickBooks on any wireless network.
Remember that all ‘wired network’ components should be protected with an Uninterruptable 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 Company file due to power-loss affecting an unprotected network switch, hub or router.
In the next part of this series we will look at many of the forms, mechanics and results of data corruption.