How To Shrink Foxhound

Question: How do I shrink and reorganize the Foxhound 4 database?

Answer: Foxhound uses SQL Anywhere for its database, and the only way to shrink the size of a SQL Anywhere database file is to unload, recreate and reload the file from scratch. The good news is, that whole process is automatic when you reinstall Foxhound on an existing installation:

• First, Foxhound creates a new, empty copy of the database file.
  

• Second, Foxhound finds the old database file,
  

• then it asks you how much of the old data you want to copy to the new database: some, all, none, just the options.
  

• Finally, Foxhound copies and loads the data, using just enough space to hold it all.
  

In this context, "shrink" means "don't copy the empty space, and don't allocate space for data that isn't copied", and "reorganize" means the copy process implicitly organizes the data as it is loaded.

Here's the step-by-step process:

Step 1: Start Foxhound 4

...if it isn't running already, that is.

In this example, the Foxhound 4 database has grown quite large:

Directory of C:\ProgramData\RisingRoad\Foxhound4 10/02/2017 07:31 PM 37,521,506,304 foxhound4.db

Step 2: Decide How Much Data To Copy

Let's say you want to save half the data, thus shrinking the file by 50%.

Foxhound doesn't understand "half", but it does understand "only copy samples recorded after yyyymmdd".

To convert "half" into "yyyymmdd", start ISQL and run this query:

All Programs - Foxhound4 - Tools - Adhoc Query Foxhound Database via ISQL SELECT CAST ( SYSTAB.count * 0.50 AS INTEGER ) AS half FROM SYSTAB WHERE SYSTAB.table_name = 'rroad_sample_set'; half ----------- 867871 -- The space used by the foxhound4.db file is more-or-less determined by the number -- of rows in the rroad_sample_set table, which contains one row for each sample -- sample recorded by the Foxhound Monitor. -- In other words, the "half" calculated here is "half the samples", not "half the bytes".

Now use "half" as the START AT value in this query:

SELECT TOP 1 START AT 867871 DATEFORMAT ( sample_header.sample_finished_at, 'yyyymmdd' ) AS yyyymmdd FROM sample_header ORDER BY sample_header.sample_set_number; yyyymmdd -------- 20170808 -- The sample_finished_at column is used to turn the row number 867871 into a yyyymmdd date. -- The sample_header view is used because Foxhound only allows SELECT statements on the -- adhoc query views (sample_header), not the underlying tables (rroad_sample_set).

Step 3: Run Foxhound's "Unsetup"

You don't have to stop Foxhound to reinstall the software, but you do have to run the special "unsetup" process to prepare for the reinstallation:

All Programs - Foxhound4 - Tools - Unsetup Foxhound -- The "unsetup" process gets rid of the Windows shortcuts and other items, -- but it leaves the Foxhound database alone... and even leaves it running.

Step 4: Run The Foxhound 4 Setup Up To The PLEASE READ THIS

In this example, the new Foxhound 4 build 4740 is used... run it all the way to the PLEASE READ THIS window:

Foxhound-Version-4-0-4740-setup.exe

Step 5: Enter The FOXHOUND4UPGRADE=yyyymmdd Value

You can copy and paste the FOXHOUND4UPGRADE=yyyymmdd value, and press Enter twice to continue:

Step 6: Wait... and Wait... and Wait Some More

It takes quite a while for 35G of data to be copied and loaded, even half of 35G.

Eventually, the upgrade process will finish, and shut down, and Foxhound 4 will be restarted with the newer, smaller, reorganized database:

Directory of C:\ProgramData\RisingRoad\Foxhound4 10/06/2017 07:25 PM 21,720,817,664 foxhound4.db

That's 42% smaller that before... not exactly half, but it meets the "more-or-less" standard :)

---