12 March 2007

Something Techies Should Know By Now

Prelude: Client hoses his operation system because he is an aggressive power-user. Techs at BestBuy and CompUSA declare that his hard drive is failing. Client buys new computer. A third (part-time?) tech recovers client data to a CD-R.

Start working the problem:

  1. CD-R disc is discovered not have important files, like outlook.pst.
  2. The hard drive was placed in a bubble wrap envelope, with no anti-static properties.
  3. The SATA connector is partially broken; someone used hot-glue to hold their connector cable.
  4. Hardware-wise the hard drive works fine.
  5. The file system could be in better shape: many zero-size files with names like, "X~@", but scandisk returns no errors.
  6. Cannot access some folders. Hmm . . .
  7. The operating system is — can you guess? — Windows XP Professional!
  8. Unlock the folders. The procedure is nicely documented at MSKB 810881.
  9. Recover 9.5 Gigabytes of personal data, including a 0.5 GB outlook.pst file.

02 March 2007

Optimize Mail.app Database

I found Hawk Wing's blog entry very notable this morning, since I use Apple's Mail.app all the time. This tip optimizes the Mail.app "Envelope" database.

From my Terminal.app:
% ls -lah ~/Library/Mail/Envelope\ Index
-rw-r--r-- 1 user admin 32M Mar 1 17:23 /Users/user/Library/Mail/Envelope Index
% sqlite3 ~/Library/Mail/Envelope\ Index vacuum;
% ls -lah ~/Library/Mail/Envelope\ Index
-rw-r--r-- 1 user admin 26M Mar 2 08:07 /Users/user/Library/Mail/Envelope Index
% exit

From http://www.sqlite.org/lang_vacuum.html:
SQL As Understood By SQLite


sql-statement ::= VACUUM [index-or-table-name]

The VACUUM command is an SQLite extension modeled after a similar command found in PostgreSQL. If VACUUM is invoked with the name of a table or index then it is suppose to clean up the named table or index. In version 1.0 of SQLite, the VACUUM command would invoke gdbm_reorganize() to clean up the backend database file.

VACUUM became a no-op when the GDBM backend was removed from SQLITE in version 2.0.0. VACUUM was reimplemented in version 2.8.1. The index or table name argument is now ignored.

When an object (table, index, or trigger) is dropped from the database, it leaves behind empty space. This makes the database file larger than it needs to be, but can speed up inserts. In time inserts and deletes can leave the database file structure fragmented, which slows down disk access to the database contents. The VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure. It is not possible to perform the same process on an attached database file.

This command will fail if there is an active transaction. This command has no effect on an in-memory database.

As of SQLite version 3.1, an alternative to using the VACUUM command is auto-vacuum mode, enabled using the auto_vacuum pragma.

This page last modified on 2007/02/13 02:03:25