So as installs of the Dude v4beta are aging they are experiencing some database issues. This is due to a 2gig limit of the SQL version embedded with the Dude. So, if your dude database is crashing, complaining about being full, or just plain running slow it may be time for a cleaning. You can easily verify this by looking in the dude/data and at the “dude” db file. If this is around 2 gig it’s time for a cleaning.
To start you will need SQLLite for your version of Windows. These are available from https://www.sqlite.org/download.html. Simply get the precompiled binary for your OS. For ease of sake Put your SQLite3 .exe in the same director as your dude database. This makes commands much simpler.
Follow these steps to clean the database:
1. Stop Dude service
Make sure to kill the dude process. Simply closing out of it does not fix. I had to go into task manager and end “dude.exe”
2. Dump DB to text with SQLite. (echo .dump | sqlite3.exe dude.db > dude.sql)
3. Rename dude.db, delete any dude.db-journal and dude.viw files. Something like dude.db.old
4. Start Dude service, this should create a new, blank dude.db
5. Stop Dude service
6. Open the dude.sql you created in step 2. Delete everything from dude.sql except for the lines that say INSERT INTO “objs” (including the lines that create the tables and indexes and stuff, just the INSERT INTO “objs” lines should be left)
7. Open dude.db with SQLite and delete everything from the objs table (DELETE FROM objs;)
8. Read old objs values into new DB (.read dude.sql)
9. Start Dude service
20,000 records took me a few hours to read back in. Just let it run. If you are curious on progress have the window open with the dude.db file. You should see the size of it growing during the read process.
4 replies on “Cleaning the Dude Database”
Dude, many thanks 🙂
Thank you for inspiring me. I’ve had a problem Dude 4.0beta3 database for a year now. It was 419259 KB in size.
Trying your solution caused all my maps to disappear.
Instead, I did the following:
1. Shutdown dude.exe
2. Make a backup of dude.db
3. Use sqlite3 dude.db
4. sqlite> DELETE FROM outages;
5. sqlite> DELETE FROM chart_values_raw;
6. sqlite> DELETE FROM chart_values_10min;
7. sqlite> DELETE FROM chart_values_2hour;
8. sqlite> DELETE FROM chart_values_1day;
9. sqlite> pragma integrity_check;
10. sqlite> vacuum;
11. sqlite> reindex;
12. sqlite> .quit
13. Start dude.exe again
My dude.db is now 46662 with all maps minus all historic outages and charts. It runs fast.
[…] back i did an article on cleaning the Dude database. Alex Nordeen has a very in-depth article on SQL Lite and it’s many features. Check out […]
I had this problem on Dude instalaltion in CHR.
I connectet via FTP to Router and copied the dude.db.
Then I did exact the same what Ted does.
Copied back the new dude.db
Everthing works fine