So I don’t think refreshing the metadata with extras disabled is doing the job. The logs were deleting about 1,000 entries each 40 seconds for Little Women 4K. I left it overnight and in the morning the server wasn’t available. So I restarted it, and optimised the DB. But after it finished the size has not changed.
Next I will try to delete the entries manually through an SQL command. Hoping that it won’t cause any orphaned entries in other tables?
I am not much of a Linux guy, but I know at least you should point plex only to folders full of media files
Now watching "/home/shayan/files/Disk Drill Enterprise 2.4.415/Disk Drill.app"
It is also recommended to separate your different types of media into separate subfolders. Then point only a Plex library of the appropriate type to the subfolder with the fitting type of media files in it.
As far as I can discern, you have only one folder where you have music, video, and software stored all together and you pointed Plex at this folder directly.
Thanks 
I’m aware of that and it does present its own issue. For example I have 3 movies that also show up as if they’re a tv show (but they’re not matched). But it’s not something I can really do in this particular scenario…I have all my media organised into proper Movies + TV/Season directories on my other server.
Yes, but you are trying to debug this server, not your other one.
After spending some time writing SQL queries I was able to bring down my DB size from the original 3.65GB to 27MB 
It’s late and I’m too tired to post the actual queries I used, but here is the gist of what I did (and everything seems to be functioning and I haven’t noticed any data loss):
metadata_items that relate to a metadata_item_id which has too many extras (find Volts handy query above)media_streams entries (when a media stream is for a media item which relates to a metadata item that doesn’t exist)media_parts entries (when a media part is for a media item which relates to a metadata item that doesn’t exist)media_items entries (when a media item entry is for a metadata item that doesn’t exist)Everything still working better?
Your bullets #2, #3, #4 sound reasonable to me. (Since I also have some orphans, maybe Plex should be doing this internally as part of maintenance.)
In #1, did you also clean up the entries in metadata_items themselves?
Yes thankfully everything seems fine so far. But I also have the “Find extras” disabled, so it’s possible with it enabled it could resurface 
In #1, did you also clean up the entries in
metadata_itemsthemselves?
Sorry yes that was a brain fart, I meant to say metadata_items and not media_streams for #1. Oh and also cleaned up metadata_relations too. Basically kept running sqlite3_analyzer and tackled big tables until I was happy 
Might not be the best SQL queries but for reference I used these, with regards to:
- Delete all entries in
metadata_itemsthat relate to ametadata_item_idwhich has too many extras (find Volts handy query above)- Delete all orphaned
media_streamsentries (when a media stream is for a media item which relates to a metadata item that doesn’t exist)- Delete all orphaned
media_partsentries (when a media part is for a media item which relates to a metadata item that doesn’t exist)- Delete all orphaned
media_itemsentries (when a media item entry is for a metadata item that doesn’t exist)
DELETE FROM metadata_relations WHERE metadata_item_id = '<offending-id>'
DELETE FROM metadata_items
WHERE id IN (
SELECT related_metadata_item_id
FROM metadata_relations
WHERE metadata_item_id = '<offending-id>'
)
DELETE FROM media_streams
WHERE media_item_id IN (
SELECT media_streams.media_item_id
FROM media_streams
INNER JOIN media_items ON media_streams.media_item_id=media_items.id
WHERE media_items.metadata_item_id NOT IN (
SELECT id
FROM metadata_items
)
)
DELETE FROM media_parts
WHERE media_item_id in (
SELECT media_parts.media_item_id
FROM media_parts INNER JOIN media_items ON media_parts.media_item_id=media_items.id
WHERE media_items.metadata_item_id NOT IN (
SELECT id
FROM metadata_items
)
)
DELETE FROM media_items
WHERE metadata_item_id NOT IN (
SELECT id
FROM metadata_items
)
Thank you for the excellent detective work and sharing your cleanup queries!
I have forwarded this thread to the developers.
There might be potential for improving the daily cleanup tasks.
Thanks everyone for all the detailed investigation.
@shayanzadeh I am looking into what may give rise to these large number of records so would love to have zip of the db when this happens and before purging any of the entries. Would you be in a position to do that and identifying which table has the issue and if possible also debug server logs covering preceding 24 hours or longer - may need to run with large number of log files for a short while - see advanced setting LogNumFiles in this support article Advanced, Hidden Server Settings | Plex Support
Cap it at 50 log files as there is a performance issue when switching log files as each fills up
What I would like to establish is if this was an old bug or is still happening and what circumstances would give rise to it
You can send the diagnostics and link to where the db zip has been uploaded to (dropbox / google drive etc) by Private Message
Thank you
Hey. Thanks for doing all this work, I believe Im suffering from the same issue. Ive gotten rid of the 3 offending IDs as well, and bullet #1 removed them, but I struggle with the remaining orphans, and my database size has only decreased a small amount. How do I remove the orphans? I do commands 2 through 4 and no errors, but they execute pretty quickly and nothing changes.
sqlite> select count(*) from metadata_items where guid like '%api.internetvideoarchive%' and id not in (select related_metadata_item_id from metadata_relations);
count(*)
5507245
sqlite>
I have 5.5 million orphaned entries. 
Please follow the instructions from @sa2000 one message up; it will help Plex with any root cause investigation.
Also please save a database backup in case it is required or requested by @sa2000. 
Check out this post (below) for a generic shotgun/cleanup script that should be effective against that issue. The SQL should clean up those items and their related entries.
Slow Query time - unable to scan or play media - #37 by jakehoms
Thanks! That script worked… 13 gigs down to 490 megs. I have database backups of the issue.
Wow -
I have now linked this underlying issue to the high memory usage and crashes after running out of memory
The databases I have looked at shows me when the issue is arising - the issue being we are adding the same extra scene or trailer to the movie over and over again during scheduled tasks refresh of metadata and over time this builds up. So I know when it happened - what I do not have are debug logs during the scheduled tasks period covering time when we were adding extras that existed already
A database I looked at today shows the last time we added the extra for the Movie "Coyote Lake (2019)` was on the 27th August 2021 and the time can be seen in the metadata_relations table
So it is possible that a bug that caused this has now been resolved - I am not sure. This needs to be determined - so I need to see what the last created_at date in the metadata_relations table for the metadata_item_id that has loads of records in the metadata_relations table. If the issue is still arising then what I need is daily morning capture of debug server logs zip over a number of days and then the database zip at the end of the week
I understand that the following action should remove the extras:
This I believe works, as I was doing this to one of my affected libraries while watching the logs remove them all, but I had so many entries it would have taken forever, so I did it manually, Im confident it would have resolved the issue if I left it for 42 years to complete. 
I’ve been running into an issue for the past month or two with my database exploding in size overnight (415MB → 1.4GB), requests to browse libraries timing out, and the server eventually running out of memory and crashing. Finally found this thread, and it turns out a single movie had ~135K extras in the 415MB database and >500K extras in the 1.4GB database. After manually deleting all the extras for that single movie using the steps provided by @shayanzadeh my database is down to 75MB, so I’m guessing this issue had been building up for longer than I realized.
One important thing I realized about @shayanzadeh’s instructions: the SQL commands in step 1 should be reversed. As is, the second command will never delete anything since the inner SELECT won’t return any IDs after the first command is run (which then causes the commands in steps 2-4 to not find any orphaned rows to delete, which I assume is the issue @DaveM was having). The first command could also probably be made to delete any orphaned rows, similar to the commands in steps 2-4. Hope this helps anyone else who needs to manually fix their database.
What I am still trying to establish if the cause of this is still happening
Do you still have that database ?
I am after the date of the last addition and what version of Plex Media Server was running and what the movie title / guid was
running something like this for the movie’s metadata id
select metadata_item_id, related_metadata_item_id, created_at from metadata_relations Where (metadata_item_id = 'xxxxx' ) order by created_at desc limit 20
That will give the last 20 additions
and then to identify the movie
select id, title, guid, refreshed_at from metadata_items Where id = 'xxxxx'
and if you can establish what version of Plex Media Server was running at the time of the last additions
Also if you happen to have any debug server logs captured of any of the times you see from this (last 1000 additions)
select metadata_item_id, related_metadata_item_id, created_at from metadata_relations Where (metadata_item_id = 'xxxxx' ) order by created_at desc LIMIT 1000
I kept copies of the database at a few different points in time, so pulling data out of those isn’t a problem. I don’t have debug logs, but I should be able to get them by restoring one of those databases and refreshing the metadata, so I’ll get back to you about that. As far as the version of the server, definitely at least 1.24.4.5081 and 1.24.5.5173 running in a jail on TrueNAS 12, though as I mentioned it could’ve easily started before that.
This looks more or less the same even if I increase the limit to 1000, just slightly different timestamps over a range of 10 seconds:
sqlite> select metadata_item_id, related_metadata_item_id, created_at from metadata_relations Where (metadata_item_id = '4025' ) order by created_at desc limit 20;
4025|545710|2021-12-02 05:03:03
4025|545615|2021-12-02 05:03:02
4025|545616|2021-12-02 05:03:02
4025|545617|2021-12-02 05:03:02
4025|545618|2021-12-02 05:03:02
4025|545619|2021-12-02 05:03:02
4025|545620|2021-12-02 05:03:02
4025|545621|2021-12-02 05:03:02
4025|545622|2021-12-02 05:03:02
4025|545623|2021-12-02 05:03:02
4025|545624|2021-12-02 05:03:02
4025|545625|2021-12-02 05:03:02
4025|545626|2021-12-02 05:03:02
4025|545627|2021-12-02 05:03:02
4025|545628|2021-12-02 05:03:02
4025|545629|2021-12-02 05:03:02
4025|545630|2021-12-02 05:03:02
4025|545631|2021-12-02 05:03:02
4025|545632|2021-12-02 05:03:02
4025|545633|2021-12-02 05:03:02
sqlite> select id, title, guid, refreshed_at from metadata_items Where id = '4025';
4025|Logan|plex://movie/5d776abffb0d55001f54faa3|2021-12-02 05:05:51
It just occurred to me that I could query for the earliest entries for an idea of when this started. The first 16 entries are from Aug 30th, 2020, then another 24 are added Aug 1st, 2021, then 96 on Aug 7th, then 384 on Sept 6th, then at least 480 on Sept 12th (only queried for the earliest 1000). Very curious that the number of additional entries increases by a multiple of 4 each time. I generally try to keep my Plex up to date with the latest version (or at least the latest published on FreshPorts), so this issue would seem to go back to at least 1.23.x.
@sa2000, I’ve PMed you the debug logs. A few more things I thought of that could be of note:
Hope this helps.
PS: as a mostly-unrelated and minor bug report: if the PMS Plugin Logs folder is missing from the Logs folder, Plex won’t start. That could probably be handled more gracefully by just creating any missing folders.
