Hi all!
This morning i realised that my database and all historical data are gone around 4 this morning…
Is there a way to restore this?
That is about the time when database housekeeping happens. It may have crashed or not completed yet.
Do you have a recent backup? How old?
Do you have media errors?
Do you have resource constraints such as low disk space, limited memory?
How do you know what time your precious data disappeared? Any other clues there?
Has this happened before or have you had any inkling it might happen?
What environment are you running this under? Any VM’s?
Before you go deleting or overwriting anything, the data might still be there. Tread carefully.
[Please post any error logs in </> forum formatted format]
Thanks for the fast reponse @IOT7712
I restored a recent backup from yesterday…Lost 12 hours only…So, problem fixed? Maybe… i feel uncomfortable not knowing why this happened…
Actually, I realised I had two updates lying yesterday evening planning to run them today: The core update and the OS update… This morning I only had the core update left… Very strange… Can this cause this kind of problem?
Answering your other questions:
I have no media errors I am aware of.
I have plenty of disk space (running HAOS on Raspi 400 with external SSD)
Never had any data loss before
I noticed that i lost data since this morning I my current data reached back to around 4 am only…
Maybe it was busy converting something when the rug was pulled. You won’t know, now that you have overwritten the evidence.
Sometimes waiting can allow the updates to complete. Other times just a restart can kick things back into gear.
Hi @IOT7712
the problem persists… This morning around 4 the database was purged again. I guess this is a bigger problem…
This time I did not restore anything… We do it your way… Can you advice?
Here are the relevant logs…
seems like your backup already had a corrupted DB. What is the media you store all your config and db on? If it’s SD card, it’s probably time to throw that away.
I am running a raspi 400 with external SSD
When I restored the data it worked normal… All historic data were availaible… until 4:12 this morning everything was corrupted again…
So you think that the restored data was corrupted @checking12 ? Why?
THe log lines you show, are mentioning irrecoverable sqllite database. Hence it’s trashing the old db and creates a new file and alll your history is unavailable in the new db.
On an SSD this is not so likely to happen and now i’m wondering why it happens. One thing that springs out, is the size of the corrupted DB, is exactly 1GB. And combining that with the momen (purge) it might also be a quota or temp space limit you’re hitting. How big is the file in the backup?
For what it’s worth, this is the reason i moved to MariaDB as data store, then you can manage bigger db easiliy (mine is about 30Gb)
From the docs:
Configuration Variables:
auto_purge boolean (Optional, default: true)
Automatically purge the database every night at 04:12 local time. Purging keeps the database from growing indefinitely, which takes up disk space and can make Home Assistant slow. If you disable auto_purge it is recommended that you create an automation to call the recorder.purge periodically.
1 Like
@stevemann
So, if I purge it manually…does this mean that the problem with the corrupted database is mitigated?
@checking12
Agreed… I did some research and the size of the file seems to be a risk factor… I wonder if I move to Maria, which seems to be the most sensible choice: Can I at least take the energi history with me?
I don’t know about corrupted database, only that if you do nothing with the recorder integration, it will default to a purge every 4:12 local time.
Just looking at this stage. Collecting information. Continue to keep the backups elsewhere for restoration purposes.
Have you made any manual yaml changes in respect of the recorder or database?
Check if your configuration.yaml is corrupted by viewing it all the way through with File Editor and look for any gibberish (particularly at the end), and also in Settings / Developer tools yaml try Check configuration option…
Look in Settings / System / Logs, and select Supervisor instead of Home Assistant Core. Anything of note?
Again in System / Storage / Disk Metrics, how is free space looking? Press the little double circle disky symbol on the right for a graphic view. Do for all drives displayed.
In System / Repairs, select the three dots. View System Information and go down to Recorder, right near the end. What is the estimated database size?
Actually, just hit copy and paste it in a reply here, and there may be other clues there that may help with isolating the problem. [not forgetting to use the forum formatting </> please]
Again in System / Updates and also System / Repairs, any issues shown?
Are the following of any assistance for your understanding of database issues?
And also
Recovering Data From A Corrupt SQLite Database and How To Corrupt An SQLite Database File
For your external SSD, what partition and format is it? How did you prepare it for use? From Linux or another computer? NTFS, or something else?
Is your system stable? Not hanging, constantly power interrupted, not freezing? No power supply issues? Any specific event you can recall that may have introduced database corruption?
Let’s try something to manually check what is happening during maintenance. Given this is a daily occurrence, try this on your database as soon as you restore it for problem isolation purposes:
From Settings / Developer tools / Actions, select Purge Entities, and use the defaults to purge and repack the database. What values were displayed for retention days?
How long did the purge process take to complete?
Go back and check the logs for clues when this completes. Is your data still intact after you complete this process? Warning: here be dragons. Be ready to restore again if this goes wrong. We are doing this as part of problem isolation. If the purge was too thorough and too many entries were purged, restore from your backup again, expediently - we’re doing this step as part of problem isolation.
Question: When doing your restore, are you restoring everything, or just the database files?
Lets go and have a peek at your database internals - get some counts of what it contains and what is actually stored there. Use the SQL-Lite Web addon, and execute the following query: [hint: cut and paste - you don’t want spelling errors here]
SELECT
statistics.metadata_id,
statistics_meta.id,
statistics_meta.statistic_id,
COUNT(*)
FROM statistics, statistics_meta
WHERE statistics.metadata_id = statistics_meta.id
GROUP BY
statistics_meta.statistic_id
ORDER BY
COUNT(*) DESC;
See anything rogue, painful or shocking that may be causing unnecessary database bloat and instability? Note: we are just looking, not changing anything with these commands. SQL is very powerful and you can mess up things catastrophically and irretrievably here if you fiddle this deep in the system internals. If you find anything of concern, such as an over-chatty sensor, just report at this time, and we can do cleanup and fix the underlying issue later.
Given the recent core update, check GitHub and report your issue if not already there.
Jumping in to let you know that I have the same issue (always around 4:00 am). But mine gets corrupted usually after one or two months of use (when it is between 1GB to 1.5GB).
It is been happing for about 6 months now and I can’t figure out the underlying issue.
I usually hate having people hijack a thread, but in this case, please follow along, adding what is similar, and what is different, and common issues will emerge to narrow it down. Thanks for contributing.
Post your system information here too [</> formatted please], before you start any changes, and it will give us a base to compare later.
See Clear up storage - Home Assistant that may help in your situation.
Comment: I note a GitHub issue has been closed without action that might be related. Have a brief look at Recorder is unable to purge a relatively big MySQL database on a different machine · Issue #105925 · home-assistant/core · GitHub
Thanks… Not trying to hijack it but rather to add to it as it may help narrow it down the issue as you mention.
I m running a MiniPC N95 with 512gb nVME. HA Core 2026.2.2 and HA OS 17.1.
But triggered by your mention above I went to look at issues at GitHub and found this issue open:
https://github.com/home-assistant/core/issues/115765
I had a brief look, and giggled at the “normal errors” bit.
It seems there are different database issues, but a systematic approach, rather than ‘try everything I find in Google and ChatGPT search’ may result in getting to the bottom of this, or at least prompt the developers to give it a bit more love, log error and return codes deeper, add tools for easier repairs, or redesign to make corruption less possible.
Databases should ‘just work’ behind the scenes, and you shouldn’t have to apply enterprise solutions for what should be a simple recording exercise, especially for home automation use.
Database should just work is true. However the normal retention period for HA and recommended with local sqllite is about 2 weeks. I doubt that anybody with 2 weeks retention has a db of more than 1Gb.
If you, like me, like to keep more data/history, then one should also be ready to switch to more robust db engine.
So to me its a choice: keep sqllite and keep retention low. Or turn retention higher and go to Maria or whatever you feel fits.
Thank you very much @IOT7712 for your thorough walk-hrough
I checked each of your points
-
config yaml looks good… I keep it uncluttered and prefer to add extra commands in separate files that I include… Those are mostly sensors nothing affecting the recorder.
-
Supervisor logs… nothing suspicious… I had influx db once but removed it month ago… aparently there are residules left that cause a warning… resolved it
-
Storage: i have an extenal SSD in an Deltaco enclosure… found no problem with it when running mdes usb but i realise that this particular enclosure has not a recommeded chipset… I ordered a new one though I do not think this is the problem. The drive itself has plenty of free space.
-
I had a number of failure- warnings with my external NAS drive. The NAS is offline during the night and this seems to generate failure warnings… In rare cases this could probably overload the small ARM processor… so I removed this drive as a precaution.
System repairs… Yes a few, unrelated; I am aware of them and mittgate them.
I ran the system the day before yesterday overnight with the new database containing 24 hours worth of data only and the purge worked flawlessly. Yesterday I recovered the corrupted database… I will later in the weekend have a better look into it.
I will also test the procedure on manually purging the database and get back to you in the next days…
Finally, thank you for putting in the time…
P.S.: more in detail going through the supervisor log i found this:
Ingress error 400 … url http://172.30.33.0:8099/api/download?filename=homeassistant/home-assistant_v2.db.corrupt.2026-02-18T…
This is the only mentioning of the corrupted db file and happened when I downloaded it…
The log shows nothing of the failure itself