My database (home-assistant_v2.db) keeps getting corrupted almost every month.
Running bare metal on a MiniPC (with internal nVME for storage).
Database size is usually around 1GB and I am using the default settings from Home Assistant. Since this issue started, I have excluded from the recorder the items that have a higher writing frequency and changed the commit_interval to 10.
One thing I noticed is that after it gets corrupted, a new database is created and this happens usually around 4:00 am. Not sure if corruption happens much before that or not, but new database seems to be created automatically around 4:00 am.
Did anyone had a similar issue? Any suggestions or insights on what I need to look for or change to figure out and solve this issue?
There are tools to analyze and even repair a sqlite dabase, but that needs to happen outside of HA. sqlite is prone to get corrupted under concurrent write access. Theoretically that should not happen and my own system now runs since its initial setup in Sep 2024 without any trouble.
Thanks… that helped a lot (from your answer also on the other topic). It is happening when there is an auto purge.
From previous corruption event, I figured out how to recover, but the issue is that it takes time and it is happening quite often. I would like to pin point the issue so it doesn’t keeping happening that frequently.
Funny thing is that I am running HA for at least 4 years (previously on a RPI4) and never had issues with that until mid 2025. But I can’t pin point what could have changed for this to start to happen.
I am running on a MiniPC (N95) with a 512Gb SSD with latest HAOS release. HA core is 2016.2.2 and HA is OS 17.1.
I wonder if the database is already corrupted and the auto purge can’t recover or if actually the purge process damages the database.
If you are a little bit knowledgeable you could export a copy of the sqlite dabase and analyze it another system. If i’s intact there is a good chance that the issue arises during the purge process.
his scfreenshot describes sqlitge3 which comes with sqlite itself.
It is a good question, but I wonder if HA creates a new database when the previous is corrupted, wouldn’t that solve the problem by itself? Unless something else keeps corrupting the newly created database.
For your suggestion for integrity test, it is a little bit above my knowledge skills, but I can try. Is it possible (or advisable) to run on terminal on the HA machine itself?
I am not sure that this is even possible as sqlite3 is not part of the console commands.
My own Linux knowledge is very limited (coming from four decades of MS Windows). If you can establish a terminal session with your HA instance from outside of HA, you can (read!) access the database and save a copy on another system for further analysis.
Maybe somebody more Linux-savant than me can step in.
Learning as I go. I just installed SQLite on windows, download the newly created database from MiniPC to Windows PC and ran the PRAGMA quick_check and integrity_check on it. Both return ok, meaning as from now they are not corrupted.
Maybe I should do the same on the previous database.
EDIT: Never mind. The previous database was not saved as .corrupt. It just disappeared.
These corruptions usually occurs because the storage media is running low.
When the database is being purged the system is actually writing a new database to a file and then when it is done deletes the old one, so you might need to be able to store a complete database on your storage media.
The same goes for backups.
The backup system will backup each container (= HA Core, Supervisor, addons/apps and so on) to each their own compressed file, which summed up can be close to the same size as the parts themself, so you need a lot of extra storage space for that, almost the same as the storage space used for the entire system.
Then to make it all worse the backup system makes a new file containing all the compressed files, so you need once more a storage space equal to the space used for the entire system.
First when the combined compressed file is done will the old parts be deleted, so for backups you need 2x times the space used by your system.
