I just saw my db file are growing, now its about 3,6GB in size. I’m om 0.64.3 release.
How do I force a cleanup?
//Sam
from the frontend you can go to the services tab and call the recorder.purge service. The command takes service data “keep_days” in JSON format to specify how many days to keep/purge from the recorder database.
Ah, I have tried it before in 0.63. But without sucess.
Now it run, but I got error in log:
ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.OperationalError) database or disk is full [SQL: 'DELETE FROM events WHERE events.time_fired < ? AND events.event_id NOT IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'] [parameters: ('2018-03-04 20:37:42.958126', 1534756, 1537832, 1537833, 1537904, 1537944, 1538083, 1538121, 1538134, 1538139, 1538316, 1540238, 1627203, 1627425, 1627429, 1627444, 1627461, 1627477, 1627479, 1627493, 1627525, 1627562, 1627599, 1627694, 1627743, 1627763, 1627764, 1627765, 1627766, 1627773, 1627774, 1627775, 1627797, 1627798, 1627799, 1627804, 1627805, 1627806, 1627825, 1627826, 1627827, 1627828, 1627833, 1627834, 1627835, 1627841, 1627845, 1627846, 1627849, 1627851, 1627852, 1627853, 1627861, 1627862, 1627867, 1627869, 1627870, 1627871, 1627872, 1627898, 1627899, 1627900, 1627901, 1627940, 1627941, 1627942, 1627943, 1627944, 1627945, 1627955, 1627957, 1627958, 1627963, 1627965, 1627966, 1627967, 1627968, 1627970, 1628010, 1628011, 1628039, 1628042, 1628045, 1628046, 1628426, 1628427, 1629272, 1629273, 1629656, 1629660, 1629773, 1629774, 1629775, 1629791, 1629792, 1629793, 1629794, 1629795, 1629802, 1629803, 1629804, 1629805, 1629806, 1629807, 1629808, 1629809, 1629810, 1629811, 1629812, 1629813, 1629814, 1629815, 1629817)] (Background on this error at: http://sqlalche.me/e/e3q8)
It seems like my SD-card are filled up during the pugre. Is there a way to purge without “caching” / logging the transaction?.. I have only 1.6GB free on SD card…
I just deleted the file… 
Worked well!
just letting you know i get this too…
missed your post, otherwise would have followed up.
Deleting the db file seems an unwanted workaround for an issue that should be fixed…
What happend after you deleted it, purge working now?
Shrinking the database will temporarily take some extra disk space because the original database is only deleted after the smaller version is ready. So when the disk gets full, there is not enough room left to shrink the database. The easiest way out of this situation is indeed to delete the database and just start over.
Recent versions of Home Assistant periodically purge the database by default, hopefully avoiding many of the cases where the database grows too big.
sure, i follow that, bit thats not the case in my setup, i have plenty of room left.
My purge is setup correctly, and i did purge before 065.5.
Ive temporarily disabled the sensors reading the file/folder, but that doesnt make a difference. Still a growing db.
Must be a 0.65.5 thing not working as it should.
Busted, I did not read your link. Your error message says disk I/O error which seems like a physical disk error 
The error message in this thread is database or disk is full which is what I wrote about.
yes, i feared all when i read that. Luckily no such thing is at hand, disk is fine…
“recorder.purge” doesn’t seem to do anything. My “home-assistant_v2.db” file is still 125 Mb.
Other suggestions would be appreciated.
Ok… going to need more info than “doesn’t seem to do anything”. Otherwise I can’t help you.
What version of HA are you running? What installation method? What other things have you tried? What’s the full command you issued from the services tab? Did it give you any notification saying it ran or gave an error? What does the log say when you ran the command?
Thanks for the help.
I am running Home Assistant Version 0.72.1 under hass.io-Resin on a Raspberry 3. Hass.io was installed from the image file: hassos_rpi3-1.7.img.gz
The data in the services tab is:
{“keep_days”:“2”, “repack”:“true”}
When I run the “recorder.purge” service, nothing happens. No error, no “success” message, nothing in home-assistant.log and the v2.db file is still 125 Mb.
Logbook? Do you mean “home-assistant.log”? There is no entry prior to 24-hours ago and I just ran the service again.
What is your configuration for recorder:?
recorder:
purge_keep_days: 1
purge_interval: 1
My 16GB SD card is nowhere near filling up, but the growing db file is going to be a nuisance when it gets into the GB size.
At a quick glance that should all work. Keep in mind the automatic purge function of the db sometimes takes 24 hours to run. So if your HA is restarted anytime within that 24 hours it restarts the 24 hours. You could be hitting an edge case of keep days and purge days both being 1, but I’m not sure. That doesn’t explain why the manual purge would not work for you. If you have the log level of recorder set to info, there should be a note in the log file on the function regardless if it was successful or not. I would recommend trying the manual command again after specifically calling out the log level of info for recorder (make sure to restart HA for changes to take) then run the command and watch the log for messages. The purge function should only take a few seconds on something that small.
I have a 30 day db that hovers around the 3.0GB mark but I don’t record all components and I shoved my db into a mariadb install on a NAS. Purge takes a few minutes for me.
Thanks for getting back to this.
This is the error in the log:
Log Details (ERROR)
Tue Aug 07 2018 23:03:19 GMT-0400 (Eastern Daylight Time)
Error executing query: (sqlite3.DatabaseError) database disk image is malformed [SQL: 'DELETE FROM events WHERE events.time_fired < ? AND events.event_id NOT IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'] [parameters: ('2018-08-06 03:03:15.536960', 2723, 6061, 11278, 11280, 12755, 16928, 49319, 50160, 50463, 53457, 54135, 54136, 54142, 54144, 54145, 54146, 54148, 54152, 54153, 54320, 54321, 54322, 54324, 54327, 54328, 55497, 55498, 55499, 55505, 55669, 55703, 55704, 55705, 55706, 55707, 55788, 57313, 57332, 57345, 57903, 58343, 58737, 59094, 59117, 59188, 59426, 60807, 61763, 62100, 62840, 63005, 63021, 66173, 69809, 69910, 69912, 69913, 69919, 69920, 69921, 69924, 69925, 69932, 69933, 69934, 69935, 69936, 69943, 69946, 69947, 69948, 69950, 69951, 69953, 69959, 69996, 70001, 70002, 70004, 70032, 70033, 70034, 70036, 70039, 70044, 70049, 70050, 70080, 70081, 70085, 70086, 70087, 70088, 70090, 70102, 70103)] (Background on this error at: http://sqlalche.me/e/4xp6)
Is there any downside to deleting the v2.db file and rebooting hass.io?
Nope. Deleting it fixes a multitude of problems with the downside only that you lose your history.
Thanks. I deleted the large(ish) v2.db file and Home Assistant created a new one right away. only a couple of kb.
Anybody knows how to add in the config repack: true, or it is enabled by default? I called purge command manually, but database was not shrinked.
recorder:
purge_keep_days: 5
purge_interval: 1
Yeah, this would solve my issue, if it were possible. My db file grows into 10GB territory within a couple weeks, and the only solution I’ve found so far aside from deleting it is to run the recorder.purge service from the dev tools with repack set to true. If I run the service without repack, the file size of my db file doesn’t change. I can tell the service is definitely running because the the .shm and .wal temporary files appear in my config directory while the purge is running, increasing in size until the process is complete. Once it’s done, however, the database file remains the same size, unless I’ve indicated the repack option, which doesn’t seem to be available to the ha config.
Now, I suppose I could create an automation to run on a schedule that calls the recorder.purge as a service and pass the repack option to it, but it seems like that shouldn’t be necessary. I’m honestly convinced that I must be doing something wrong, but just haven’t figured out the proper configuration to keep my database file under control.