Lost all my charts after cleaning orphaned entities with Spook

silkyclouds · March 30, 2025, 8:43am

Hi everyone,

I’m reaching out because I’m completely stuck after doing some long-overdue cleanup on my Home Assistant instance. For reasons I still don’t understand, I suddenly ended up with over 43,000 entities in my setup, including nearly 4,000 lights and 4,000 media players. Needless to say, I don’t live in a shopping mall, and something was clearly wrong.

Initial situation

Before cleanup, these were the numbers I was seeing:

• Entities: 43,954

• Lights: 3,902

• Media Players: 3,918

• Sensors: 23,654

• Database size (home-assistant_v2.db): 1.3 GB

Performance had become sluggish, restarts were slow, and the UI felt increasingly unresponsive.

Cleanup with Spook

I installed the Spook integration by Frenck, which turned out to be an excellent tool. I used it to identify and remove orphaned entities — those that were no longer linked to any device or integration. The cleanup went smoothly, and the numbers returned to something much more reasonable:

• Entities: 1,612

• Lights: 56

• Media Players: 18

• Sensors: 608

These numbers now reflect what I actually have installed in my home.

Purging the database

To go a step further, I also used Spook to identify remaining orphaned entities in the database, and I manually triggered a purge with the following service call:

service: recorder.purge_entities
data:
  keep_days: 0
  repack: true
  entity_globs:
    - sensor.*
    - media_player.*

This was intended to clear historical data related to those ghost entities and reduce database size.

The problem

It seemed like everything had worked — Home Assistant now boots up faster, the entity count is clean, and the UI is responsive again.

But the database? It actually grew in size.

[core-ssh ~]$ du -sh /config/home-assistant_v2.db
1.4G	/config/home-assistant_v2.db

Even worse, now all of my charts are flat. No temperature history, no power usage, no pellet container levels — nothing. Even charts for today, which should be generating new data, show flat lines.

Here are some screenshots to show what I mean:

• Energy dashboard: completely flat and empty of all data !

• Room temperature history: blank

• Pellet container level: no variation, 64% filled, but look at the chart line

(All those graphs used to work fine before the cleanup.)

My questions

• What could have caused the charts to stop working, even for new data?

• Is there a way to “reactivate” or reset the recording for these entities?

• Should I manually delete the database and let HA recreate a fresh one?

• How can I safely clean up my system without losing current-day graphs?

Any insight would be hugely appreciated. I really want to keep my setup lean, but not at the cost of losing all visibility into what’s happening in my home.

Thanks in advance to anyone who can help, and special thanks to Frenck for building such a useful tool — even if I may have broken something along the way.

To be honest, keeping the historical data is “nice to have” without being crucial, so would deleting the db and restarting HA be enough to fix this issue ?

Thanks !

ddaniel · March 30, 2025, 9:05am

Stop home assistant and rename database file and then start ha. If something goes wrong you can revert it back.

silkyclouds · March 30, 2025, 9:14am

Yes this is what I did and everything is now working again. I guess the recorder broke my DB somehow… whatever. I know I dis consume to much electricity in the last few years anyway, i’ll start fresh !

ddaniel · March 30, 2025, 9:15am

Well It’s not then end of the world. It’s happens.

silkyclouds · March 31, 2025, 6:28am

Unfortunately, after a day of using HA with the new db, the problem persists.

Even more weird is the fact it seem to have worked between 11am and 12pm yesterday morning, and stopped working after that.

any other ideas, someone ?

silkyclouds · March 31, 2025, 7:03am

I investigated a little more :

Yesterday morning:

I renamed my existing database to start fresh:

mv /config/home-assistant_v2.db /config/home-assistant_v2.db_backup

I restarted Home Assistant, allowing it to recreate a fresh, empty home-assistant_v2.db.
The new database was initially 3.2MB — all good at that point.

Today, less than 24h later, the database has exploded in size again:

-rw-r--r-- 1 root root 387.4M Mar 31 08:20 home-assistant_v2.db

And yet… no charts are working. All dashboards are flat — no temperature, no power usage, no long-term statistics — even for the entities I confirmed are active and reporting correctly.

I copied the DB to my Mac and started investigating using sqlite3.

SELECT event_type, COUNT(*) FROM events GROUP BY event_type ORDER BY COUNT(*) DESC;

Result :

event_type | COUNT(*)
-----------+---------
           | 752984

There are over 750,000 events with no event_type . Completely abnormal.

Checked the statistics table:

SELECT * FROM statistics LIMIT 5;

There are plenty of rows with valid mean, min, max values and metadata_id, like:

id | created            | metadata_id | start             | mean | min | max
---+--------------------+-------------+-------------------+------+-----+-----
...| 1743400813.92341   | 270         | 1743397200.0      | 90.0 | 90  | 90

But…
statistics_meta table shows only IDs — no visible link to entities:

SELECT id, statistic_id FROM statistics_meta;

Output looks like this:

id  | statistic_id
----+---------------
270 | (not shown)
271 | (not shown)
...

So I have data, but it’s not associated with any entity name, making it completely useless for rendering graphs in the UI.

Seriously, what should I do at this point ? I’m confused

silkyclouds · April 14, 2025, 10:57am

I reproduced this issue by simply running a recorder purge again, keeping only the last 3 days…and, again, home assistant ain’t collecting any new data anymore.

the problem is I’ve had some backup issues, and the only backup I was able to restore dates from AFTER the momment the database was screwed.

As previously said, even deleting the db doesn’t fix the issue, it gets re-created, but home assistant wont write any historical data to it.

Is there something I can do to get this escalated somehow as it looks like a bug to me, since it can be reproduced ?