I need instructions on how to drop (or at least delete large portions) of the historical data.
The forum mentions a thread on database maintenance and cleanup procedures followed by the use of vacuum, but I cannot locate that thread.
This 2gb issue really needs some attention.
I wrote these instructions long ago...
An easy way to vacuum the database is to copy dude.db and sqlite3.exe into a new folder "somewhere other than the dude folder" this will let you modify a copy of dude.db instead of the original. Make a shortcut to sqlite3.exe, edit the properties of the shortcut so that dude.db is on the command line.
"C:\Documents and Settings\person\Desktop\sqlite3.exe" dude.db
Here is a screenshot of the process...
I REALLY appreciate the prompt response… Thanks.
From the other post, it appeared that there might be a more in depth process.
That said, I get a Primary key error as follows:
SQLite version 3.7.14 2012-09-03 15:42:36
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite> vacuum;
Error: PRIMARY KEY must be unique
sqlite>
I don’t know where to turn other than completely remove all data but I have a considerable number of items being watched. Is there a table description or database dictionary anywhere ?
Wow primary key is corrupt you will need to go Google how to make it unique (although I know nothing about this), the database is also compressed so you need to extract it,some where on here someone mentions the tool they used… probably easier to start over or find a backup before you had issues… bummer.
Try this:
http://community.spiceworks.com/how_to/show/1468
HTH,
Followed the link and learned a few things.
1 - Apparently, there is a object created for EVERY MIB entry for EVERY map item.
2 - If the same item appears on two different maps, they are treated as unique items.
3 - I have 21,000+ items in my objs table.
4 - Apparently, there is something about the objects stored elsewhere. If I delete the db and recreate the schema then import just the objs table, the passwords, maps and all other configs are gone as well. 
That is an impressive number of things to monitor and yes every map item is unique. Many times when I need a copy of an object on another map I make it a static object and leave the real on on the main map. I didn’t get to read about primary key but it sounds like you might have to start over?
@duanes1967
First of all make a backup copy of your Dude database.
Next connect to database with ‘SQLite’ utility (or try this one: http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index)
and execute following SQL statements, one by one:
SELECT id FROM "objs" GROUP BY id HAVING (COUNT(id) > 1);
SELECT timeAndServiceID FROM "outages" GROUP BY id HAVING (COUNT(timeAndServiceID) > 1);
SELECT sourceIDandTime FROM "chart_values_raw" GROUP BY id HAVING (COUNT(sourceIDandTime) > 1);
SELECT sourceIDandTime FROM "chart_values_1day" GROUP BY id HAVING (COUNT(sourceIDandTime) > 1);
SELECT sourceIDandTime FROM "chart_values_2hour" GROUP BY id HAVING (COUNT(sourceIDandTime) > 1);
SELECT sourceIDandTime FROM "chart_values_10min" GROUP BY id HAVING (COUNT(sourceIDandTime) > 1);
Every executed SQL SELECT statement will show you duplicated ‘primary key’ records in specified table.
Write down values returned for every table and delete duplicated records.
Finally execute :
REINDEX;
VACUUM;
HTH,
A HUGE thanks to everyone helping.It looks like the db file is corrupted. I think that the .dump worked, so maybe hunting the duplicates from there might work although I'm still not sure my the maps and all config data was missing after I killed the db and then recreated the tables (from .schema) and then re-imported only the objs table. It looked like the blob data contained each item's info about maps, options etc.
sqlite> delete from chart_values_raw where sourceidandtime in (0, 1);
sqlite> SELECT timeAndServiceID id FROM "outages" GROUP BY id HAVING (COUNT(timeAndServiceID) > 1);
sqlite> SELECT sourceIDandTime id FROM "chart_values_raw" GROUP BY id HAVING (COUNT(sourceIDandTime) > 1);
0
1
0
0
0
1
1
0
0
0
1
0
1
0
0
Error: database disk image is malformed
sqlite> delete from chart_values_raw where sourceidandtime = '0';
sqlite> delete from chart_values_raw where sourceidandtime = "0";
sqlite> SELECT sourceIDandTime id FROM "chart_values_raw" GROUP BY id HAVING (COUNT(sourceIDandTime) > 1);
0
1
0
0
0
1
1
0
0
0
1
0
1
0
0
Error: database disk image is malformed
Try to dump database (structure and data) as SQL script, then import it to newly created database.
Next peform all steps described in my previous post.
HTH,
Hello,
The Dude 6.42.7 running on RB750Gr3
Command /dude vacuum-db works fine in CLI.
But it can not be correctly scheduled. Checkbox “Policy: Dude” can not be saved.
Have you seen any real impact of vacuuming so it makes sense to schedule it for you?
Yes.
I am monitoring OpenWRT access points interfaces.
OpenWrt 18.06-SNAPSHOT r7407-1cd945ea22
Without periodic vacuuming there “ghost” interfaces occurs like (42949672985) instead of normal like wlan0, wlan1
After vacuuming all OK.
Hi All,
I have a Dude server that is sitting at 8 GB running. What is the best way to purge old data?
Bill
any solution ? I’m also wanting to clean old data
The Wiki on this:
https://wiki.mikrotik.com/wiki/Manual:The_Dude_v6/db_vacuum
Also have a look at this script to backup and vacuum:
Hello,
vacumm not helped. I found another solutin and it’s great now 
here is a solution;
http://www.mtin.net/blog/cleaning-the-dude-database/