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 :)
