Last Update: February 4, 2022
DATABASE MAINTENANCE
Table of Contents
Backup/Pack/Index
Backup
Q-Law is a client-server type system that relies heavily on data stored within a Database. Databases offer many advantages to storing data in things like an Excel spreadsheet, or file cabinets, but in order to perform at their peak, regular maintenance must be performed. Before performing any of the maintenance functions, it is recommended a Q-Law backup be performed. Below are the steps:
- From the Q-Law Main Menu, click Maintenance.
- Click the System Maintenance Tab
- Click Backup-Pack-Index
- Click Backup
Pack
Years ago, Mac users were introduced to a little waste basket icon on their desktops called “Trash” (originally it was called, wait for it, “Wastebasket”). This was a placeholder for items users wanted to delete, they dragged them to the trash, and voila, they were gone. However, they weren’t deleted until the trash was “emptied,” which protected against accidental deletion. The idea caught on, and similar concepts appeared on several different operating systems, the most familiar of which would be the “Recycle Bin” on Windows based operating systems. Deletion within Q-Law, and within most database-based systems, is handled in a similar fashion as the concept Apple introduced almost 30 years ago. When data is deleted in Q-Law, the entry is marked for deletion in the table, but is not actually removed. This allows data to be recovered if it’s accidentally deleted, however, unlike the “Trash” and “Recycle Bin” which have set sizes where they begin removing items, there is no limit to the number of deleted records in the Q-Law tables, which can eventually lead to speed and performance issues. The cure is a function called “Packing”, which is essentially Q-Law’s way of emptying the trash.
When a table is packed, all items marked for deletion are permanently removed from the system, there is no fall back, and there is no way to recover a deleted item after it’s been packed, other than restoring from a backup. Packing also rebuilds the indexes within the tables (Indexes will be covered in the next section). It is Q-Soft’s recommendation that the system be packed, in its entirety, at least once a month. Below are the steps to pack the system:
- From the Q-Law Main Menu, click Maintenance.
- Click the System Maintenance Tab
- Click Backup-Pack-Index
- Click Pack All
Index
Much like the Index in the back of a book, a table index is a snapshot of the data within a table stored in a way that makes it much easier, and faster for the system to search. The time saved using indexed records within the system can range from milliseconds to minutes. Considering the indexes contain basic snippets of data tied to a location in the table, and the system doesn’t bother authenticating whether or not the record it’s directed to is actually correct, it is possible for a corrupted index to cause odd data results when viewing files or generating reports. Corrupted indexes can also generate errors within Q-Law, however, because of their nature, it’s possible for a user to see the error once, and then not see it again for a substantial amount of time. The only way to properly correct a damaged index is to recreate it. Packing will rebuild the index, which means it will use the existing index structure and just rewrite the entries. Rebuilding will completely delete the old index and rebuild it from scratch. Currently, it is only possible to rebuild one index at a time, but it is uncommon to have more than a few tables require a rebuild at once. Below are the steps.
- From the Q-Law Main Menu, click Maintenance.
- Click the System Maintenance Tab
- Click Backup-Pack-Index
- Click Index
- Double Click, or Click the table to rebuild, and then click Select Record.
Some tables can take a long time to rebuild the index, typically Deadline (which houses all Tasks on files in the system) takes the longest. 15 minutes is about average for a large table’s index to be rebuilt.
Lock Q-LawE
While performing maintenance, it is recommended the system be locked. Locking Q-Law prevents users from logging in and accessing the database tables while maintenance is being performed. If a user were to attempt to open a table that is being packed, for instance, severe damage can occur and the only way to recover would be to restore the table from a backup.
In order to lock Q-Law, perform the following steps (NOTE: Only users with security level 1 can perform the below):
- From the Q-Law Main Menu, click Maintenance.
- Click the System Maintenance Tab
- Click Backup-Pack-Index
- Click Allow Logins
- In the Lock field, type in Y
- Press CTRL + W to save and exit.
When users attempt to login, they will be presented with a screen that will require a special password to unlock the system. The password can be set at the above screen.
Database Corruption
A few weeks ago, San Diego underwent a massive blackout. A technician in Arizona was swapping out a piece of equipment, something shorted, and a tidal wave of grid shutdowns followed, causing 5 million people to lose power. When power is suddenly lost in the middle of a write operation, file corruption is typically the result. In Q-Law, this corruption manifests itself as a Data Corruption error. Power loss is just one of the causes, the most common cause is faulty network equipment, typically a temperamental network card in a workstation or server. Any interruption to the write operations when writing to the Q-Law database will cause corruption, but thankfully, data corruption is simple to repair, and typically the damage and lost data is minimal. Below are the steps to recover a damaged table.
- From the Q-Law Main Menu, click Maintenance.
- Click the System Maintenance Tab
- Click Backup-Pack-Index
- Click Test Databases
- The system will attempt to open all the Q-Law tables, if any are corrupted, the system will note the appropriate table. Be sure to jot down the names of any corrupted tables. After you have the names of the tables, proceed to the next step
- Click Data Repair
- Click Select Records
- Browse to and select the corrupted table (typically located within the Data folder, the corruption error will also indicate the path.)
- Click Error Scan/Repair
The system will correct the table and display a short message briefly identifying what the issue was. After the repair is completed, it is highly recommended the table have its index rebuilt as well.
After recovering from a damaged table, it is recommended users check the files they were working in prior to the corruption in order to re-enter any lost entries. It is common for the last entry to get deleted, or lost, during corruption, typically a note entry, or an entry into the Work Log, so a few minutes making sure everything is kosher is recommended.
Preventing data corruption is difficult as there are several causes, and it is impossible to know WHEN something is going to fail, resulting in corruption. That said, battery backups for network devices, workstations, and the server, can prevent data loss in the event of a power outage, and the occasional network health test may uncover a card that is on the border of failure.
Maintenance Log
With all the above maintenance, the system logs the results within the Maintenance Log. It is sometimes useful to refer to this log, especially when it comes to data corruption, to see what has been done, when the last time maintenance has been performed, and in the case of corrupt tables, exactly which tables are corrupt. The Maintenance log is found by performing the below steps.
- From the Q-Law Main Menu, click Maintenance.
- Click the System Maintenance Tab
- Click Backup-Pack-Index
- Click View Maint Log