Had a problem recently where my normally well behaved 100MB HA database leapt in size to over 1GB over a couple of weeks. No idea what was causing it so I turned to the community where there was so much information spread over several threads and hundreds of posts from lots of well-informed people – unfortunately most of it way over my head or out of date.
Thanks to this thread https://community.home-assistant.io/t/how-to-keep-your-recorder-database-size-under-control and good info from @denilsonsa, @jeedewee, @CaptTom I found and fixed my issue. Thought I’d write it down in one place in the hope it may help someone else with a similar issue save quite a bit of reading. I’m no expert but this worked for me – happy to hear about better ways.
I have a ‘vanilla’ install of Home Assistant Operating system on an RPI 4 4GB, on my status and maintenance dashboard there’s a card with the sensor.home_assistant_v2_db_size entity so I can keep an eye on how the database is changing. I’ve seen stuff about the database getting huge so am fairly ruthless about disabling entities I don’t need and excluding others from the History. Here’s the relevant bit of my configuration.yaml
recorder:
purge_keep_days: 7
commit_interval: 30
exclude:
domains:
- automation
- updater
- weather
- sun
- light
entity_globs:
- sensor.hall_sensor*
- sensor.landing_sensor*
entities:
- binary_sensor.hall_sensor_motion
- binary_sensor.landing_sensor_motion
So what caused the database to grow to over 1GB? Here’s how I found out and fixed things:
Step 1 – take a look inside the database
Step 2 – find the entity or entities taking up all the space
Step 3 – Exclude them from the recorder if you don’t need their history
Step 4 – Get rid of their ’spam’ from the database
Step 5 – Reclaim the space they were using – shrink the database back down to size
Step 1 – take a look inside the database
Need to install the SQLite Web add-on – Settings, Add-ons, Blue Button for Add-On store. Make sure to select ‘Show in sidebar’
I needed a power cycle to get the add-on working.
SQLite Web is crazy powerful and I know very little about SQL databases – fortunately don’t need to.
Step 2 – find the entity or entities taking up all the space
Fire up SQLite Web and Select the States heading – in the list on the left
Select Query from the tabs along the top
Copy this SQL incantation into the Query box
SELECT
COUNT(*) AS cnt,
COUNT(*) * 100 / (SELECT COUNT(*) FROM states) AS cnt_pct,
states_meta.entity_id
FROM states
INNER JOIN states_meta ON states.metadata_id=states_meta.metadata_id
GROUP BY states_meta.entity_id
ORDER BY cnt DESC
and Execute - green button below Query box
In a few seconds a list will appear – the entities taking up the most space are at the top of the list. In my case two entities had counts that were more than 100 times higher than everything else – fairly easy to spot!
Step 3 – Exclude them from the recorder
If you really, really need their history then you need to start diagnosing why the entity is so ‘busy’ – beyond the scope of this guide - good luck.
If you don’t need the entity at all just disable it:
Settings - Devices & Services - Entities (Along the top) - tick the rogue entity and ‘Disable Selected’ (at the top in blue)
Alternatively add the entity to the Recorder exclude entities section in configuration.yaml
recorder:
purge_keep_days: 7
commit_interval: 30
exclude:
domains:
- automation
- updater
- weather
- sun
- light
entity_globs:
- sensor.hall_sensor*
- sensor.landing_sensor*
entities:
- binary_sensor.hall_sensor_motion
- binary_sensor.landing_sensor_motion
- binary_sensor.ble_opening_f5d75c6f7c31 # New entry rogue entity
- sensor.ble_rssi_f5d75c6f7c31 # New entry rogue entity
Need a restart as usual after making config changes. Those entities won’t add to the database in future and it maybe that in due course HA will remove them and reclaim their space but I wanted to get my database back to normal now.
Step 4 – Get rid of their ’spam’ from the database
HA has a Service that will remove entities from the database – Recorder: Purge Entities
It can be run from the UI (Thank you!) Developer Tools – Services
Add the problem entity or entities to the Targets section – leave everything else unchecked – click ‘Call Service’
There’s no UI feedback but you can check things are going on by looking at Settings - System - Hardware. On my RPI4 the Processor use jumped to about 25% from its normal 3-4%. After about 1-2mins it returned to normal.
Back to SQLite Web and rerun the Query from section 2 – the problem entities should have gone from the list – good news - the database size hasn’t changed though.
Step 5 – Reclaim the space they were using – shrink the database back down to size
Another service – Recorder: Purge will get the database back to size
Developer Tools - Services – Recorder:Purge
Make sure to tick ‘Repack’ and set ‘Days to Keep’ to your usual Days to Keep – 10 default, 7 in my case. Leave Apply Filter unchecked. Don’t worry this action won’t get rid of the Energy Dashboard data (which can be much older than 7 or 10 days)
Again no UI feedback - check the hardware (as above) to see something is happening. Only took a minute on my system and database size was 30 MB, down from 1016 MB – so relieved, can sleep again!
Appreciate that for many in the community this is all very simple and obvious - but it wasn’t for me. At least now I’ll know where to look if this ever happens again and I’ve forgotten the details 
. Sometimes you do not find something in the RTFM (most likely it’s there but you know…) and these kinds of posts with examples puts me on the right track.