Do you observe a smaller filesize of a db-file?
Definitely!
Using the recorder integration (Recorder - Home Assistant) I frequently add and remove entities…
Recorder integration allows to define “exclude” rules which I am successfully using.
Surely number of entities selected to be recorded defines a db size.
What I am saying that calling the “purge” service manually (from Dev tools) does not seem to cause any changes. Tried with “repack” option too.
What you’ve posted here is not even remotely correct. Where did you get this information?
1 Like
So no way to detect ?
Sometimes due to unknown problems (or bugs) a DB size grows significantly - even when there is no changes in setup:
- assume I deleted db-file;
- then the db-file is created on HA startup; file size is growing for some time (probably for 10 days);
- then a file size is same, not changing;
- and sometimes a file size changes significantly - within several hours.
Sometimes it happens along with a huge cpu load; a “top” command reports that some “python” process has this huge load. So probably this “python huge cpu load” issue could be a reason.
Then - to get rid of this “extra” addition to the file size - I wanted to use this “purge” service. But as I said before, do not see any results of this service call.
I can only agree upon this, have couple of times used purge / purge-repack , both on entity/domän level, and days.
If you doubt, go to developer tools, choose purge-repack(don’t forget the blue toggle to the right) / keep 2 days , call-service … while you monitor ( home-assistant_v2.db + home-assistant_v2.wall ) either in ha-file-editor, or over samba
EDIT: @Tsar , No i can’t tell, just make an assumption, i give it some time ( watching it “bump” around ) , sometimes trying to emagine which entities/domän it’s currently processing 
. you know, like the old days in the 80th, where one was listening to the communication over the phone-knuckle-equipment
As I said - no any changes in my case.
ok, well i don’t know what you have in your DB, how yous recorder is configured etc …, but if you have a DB-file, how small / big is it ? , if we assume it’s more than i.e 50-100mb, and you choose to purge-ALL, keep 1-2 days … and it does not get smaller, id say something is weird
im not sure what the “default purge-interval” is now , but if you, as you say delete the .db-file , it start over from 0 +( what’s in the .wall file ) , until the day (default-purge , lets say 10 days ) and start to “keep it steady”, purge every days ( dump, entries older than 10days ) … SO you do have 10 days, always ( if you use default settings, probably more ) , AND you tell me your DB-file don’t get Smaller, if you choose, Purge-Keep-2 days-only–Repack
Sorry , you can’t convince me, unless you have some other “card-in-your-sleeve” 
No, I did not specified an interval when calling the service. I think it was same default 10 days.
I described my situation very clear: some strange increase of file size happened and I wanted to clear all unneeded data. I believed that this “repack” function will do it.
Anyway, as I already told above - no ANY changes of file size, no any additional CPU load (“top” command), no any log messages. Seems that the service is not started at all. That is why I asked - are any changes are visible to a user when the service is started.
Last time DB was about 6GB; had to delete it after a recent crush.
And not going to convince anyone))
OK, yes so it wil just purge, like it’s todays purge … and you wont notice much, because you still have 10 days
I will try to call the service with 2 days interval in several days, when DB size will be constant (with 10 days interval). Without repack, just to see if a file size decreased.
So, this service (repack) will not help to remove unneeded data added due to a bug?
If you observed this increase over the last 10 days and your purge_keep_days is set to 10 days then it makes sense that there’s no change in DB size. As the doc on that service says:
Call the service
recorder.purgeto start a purge task which deletes events and states older than x days, according tokeep_daysservice data
The new data is obviously less then 10 days old since you’ve observed the increase over 10 days. So it won’t get purged, its not older then the threshold.
Btw, do you have auto_purge and auto_repack set to true? That is the default so if haven’t set them to false then that’s what they are. If so you should rarely expect a significant change calling this service unless you change the keep_days in the service call. Your DB is already purged of data older then purge_keep_days and repacked every night at 4AM already.
1 Like
So that’s what it’s doing at this hour ? , i have sometimes wondered WTH it’s doing early in the morning … 1 time i was about to open a Topic 
Here is my recorder.yaml:
recorder:
auto_purge: true
purge_keep_days: 10
…
If no bug occurs:
- db file is deleted; HA started, new db file is created;
- db file size is increasing;
- after 10 days the db file size stops increasing due to that “04:00 AM” call.
Right?
But - here I described my case.
Db file size is not supposed to increase significantly after 10 days when there is no changes in HA. But it is rapidly increasing largely.
Ok, this could happen due to some integration, I do not know. And this is not a thread to discuss a possible reason of this bug, I think.
But - I believed that the “purge+repack” service could remove that “wrongly added” data.
So far I had to delete the whole db file. Normally the file size in not bigger than ~4GB; but last time due to that bug it was ~6GB, before it was ~15GB…
And as I said - tried to call this service with repack and got no visible changes…
Yes true, anything/many-thing could cause it to suddenly grow, an “ha-update” a “device/lovelace-card/integration-update” , some manual made script/automation/etc etc.change in same …
Whatever you can imagine that would cause an i.e “event” or i.e “State” Change !
“Events And States” changes is the out-most biggest reasons for the DB to increase, so a wrong automation/script etc that causes millions of “New entries” in event/state table, maybe even cause of a chain-reaction in “state-changes” “event” etc.
So to your question, Yes you can use the sqlite-addon ( beside the usual logfiles ) to figure out if it’s a specific integration/device/entities which causes a sudden increase ( and actually even delete entries in sqlite, but also with purge by "Domän/Area/Entities )
Check this out ( and above this ) and also The Purge in Docs.
EDIT: If you choose to delete via “command” you can also after this run “Repack” to reduce the DB-file, instantly, othervice it will/should go back to “real-normal-size”
This is HA in container; there are no automatically installed updates.
All my automations do not create new data, they may only change input_boolean helpers.
I suspect only one thing: when a huge CPU load occurs (python, as it is reported by “top”), the HA is unable to process data properly, so DB is flooded by some trash. For testing, I switched off almost all my integrations (Life360, Traccar, Companion app, Met.no weather, netdata, open hardware monitor, asuswrt, xiaomi, …), but still observe this huge CPU load sometimes. Reinstalling Debian does not help.
I will read it, thank you!
But my initial question was - may I use the “purge” service to remove this wrongly added data ; I did not mean using SQL which I really do not know)))
Yes you can , if you know which entities/ or domän / or area , unfortunately it’s not as easy to do, if you don’t know what was filling up the DB, and here sqlite-addon is a good “companion” to penetrate the event/state table ( removing newest entries etc.)
With purge you should also be able to Purge specific, but i’ve never tried with a “list” of entities , only individual entities and Domäns … i purged 1 time ALL by Domäns, i.e. Light/Switches/Device_Trackers etc etc. 1 by 1, with purge-interval “Lower than default”
But i guess im done with that part , i have restricted Recoder to “include” , so i just have to include what ever i want it to record
As I understood, if some entity (-ies) is specified, then the service will purge only this particular entity (means - remove all records which are older than “today - purge_interval”).
This is not what I need; I think that DB is supposed to contain only entities which are “allowed to be recorded” in the Recorder’s settings (I myself use only “exclude” options). So, since I need these “allowed” entities’ data - I am not supposed to delete them.
What I need is to get rid of a trash added due to a bug, wrote about it many times. I do not think that this trash is some “allowed” entity’s data - in this case this trash should be deleted automatically on 04:00 AM, which does not happen.
Your database will only change size on repacks, which by default occurs on the 2nd sunday of each month.