I’ve recently encountered an issue with my Home Assistant set-up which has been running fine for several years. A few days ago I noticed that all my recent and long term history had disappeared, and only stats from around 4am that day were available. I then discovered that the recorder database had been resized to close to zero. Fortunately I’ve been taking daily back-ups so was able to restore my old database to the day before; but since then the same thing has been happening. Every day around 4am my recorder database is being reset to zero.
After lots of investigation it looks to be a function of the routine Purge action performed by HA every day at around 4am; but I can’t understand why it has started to delete everything, and not just the routine older short term data.
I have noticed however that my database has increased in size in recent weeks (was running at around 1.6GB, but has grown to 2.3GB. I believe that may be because I added in the AdGuard integration and (I suspect) that has generated a bunch of extra sensor data. I’ve now tried to exclude this from the recorder database via configuration.yaml, but this doesn’t yet appear to have made much difference.
I can recreate the problem by running the Recorder:Purge action from the Developer Tools. Which results in the same thing: database gets deleted! This happens whether I set the action to “Repack” or not.
I can find nothing related to this in the log files. Mystery!
I’ve seen other posts around problems with the Purge function from last year, but none quite like what is happening to me; so I am now stuck on what to try next.
Can anyone help?
I’m running latest versions of HAOS as VM within Proxmox
Core 2025.1.4
Supervisor 2024.12.3
OS 14.2
I’ll take a shot at it. First of all, it sounds like the DB isn’t getting deleted so much as purged farther back than you wanted. So of course check your purge_keep_days. Not sure what happens if that’s set to zero.
As for the increase in the DB size, that’s not uncommon if you don’t carefully manage your excludes. Check out this thread:
Since it sounds like you’re losing data anyway, maybe just delete the home-assistant_v2.db file entirely. HA will re-create it. Of course it’s probably best to shut down HA Core, then delete it, then re-start.
Anyway, that’s where I’d start. Maybe someone else has experienced this and can offer more specific troubleshooting steps.
Thanks @CaptTom. Agree it is could amount to a heavy purge, rarher than the db being deleted but the result is the same. After Purge has run I have Zero days retention (so no recent stats history and no LTS… which i didnt think Purge should affect). Seems like something else is going on.
I’ve not (until now) tried to fiddle with any of the default Purge settings, so 10 days default should still be the case. I have however added this into my config.yaml file to be doubly sure. But if i manually trigger purge via Developer tools and set to 10 days, the full delete still happens, so I’m not hopeful that this is going to make any difference, and certainly doesnt explain why this problem has just started to arise.
The only thing that i think has happened (changed) is that AdGuard has started to flood the db with stats and grow it size during last 2 weeks. I’ve now tried to prevent that with the Exclude setting in config.yaml.
I could just start afresh and see what happens but I’d really like to keep my db, especially for long term energy/solar tracking / comparison (this year vs last).
Yeah, something odd going on there. Keep a good copy of the most recent non-purged DB somewhere safe! I’m wondering if it would be helpful to open a copy of the DB outside of HA and do a repair there. At least that would let you extract the data you want to keep.
I gave up on the Recorder DB for long-term data. It’s really better at maintaining recent data. I have data I’d like to retain for years, too. I store those in comma-delimited text files. I can open, analyze and manipulate them with just about any application I want, outside of HA.
@CaptTom I may do that. I noticed you are using MariaDB. I was tempted to make that move a year or so ago, but decided not to complicate my life unnecessarily. Maybe it’s time to bite that bullet afterall.
I’ll wait and see if there are any other ideas via this thread. Thanks.
Ah, sorry I misunderstood from what I read in the link you shared.
Anyhow, I may have made a minor breakthrough. Your suggestion of taking an off-line copy of the db and doing a repair there with view to keeping a separate copy led me this post:
I followed the recommendations posted there to review / fix database errors and then put the resultant file back into Home Assistant. I’ve now run Purge and “hey presto”… my data didn’t disappear; but my database shrunk back down to the size it was a few weeks ago before it started its rapid inflation.
This may be a fix after all. Let’s see what happens after 4am tomorrow.
Thanks for all your suggestions.
I’ll share feedback tomorrow on whether the mystery if disappearing data has finally been resolved.
Thanks for posting an update. Coincidentally, a funny thing just happened to me…
I did a full backup, then a Supervisor update to 2024.12.3. When HA came back up, all the graphs showed no data. In fact, the database file in the config folder was tiny, under 200 KB. So I shut down HA and went out to the backup file.
The database file was not in the backup. It was in yesterday’s backup, and when I copied that one over and re-started HA, I got an indication that the database hadn’t been closed properly (probably because I hadn’t deleted the temporary database files) but things started working again.
At this point it looks like I’ve lost a few days’ worth of activity. But as I said, no big deal since the long-term data I care about is stored in .csv text files. Ironically I usually copy the database off to a temp file for review and clean-up whenever I update HA Core, but I always figured the Supervisor update was low risk. I guess not.
Lesson learned. Make a clean copy of the DB somewhere before doing ANY updates.
I can confirm that the after the auto Purge this morning, all my stats remained in place, including LTS. So the mystery appears to have been solved. Some sort of glitch with recorder database which was then easily solved using the steps above.
Wasn’t at all obvious, but your suggestions @CaptTom pointed me in the right direction.