Enormous size of database file

Hello! I’m using PMS on Ubuntu 18.04. Now my database file (com.plexapp.plugins.library.db) has size 349 GB. Maybe it wanted to be even bigger, but now I don’t have any space on system HDD at all. Why this have happened and what can I do?

Server Version#: 1.19.4.2935-79e214ead

What do your scheduled task settings look like (Settings > Scheduled Tasks)?

My /var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db is 198MB on a server with a movie library (819 items), a tv-show library (2170 episodes) and a small audiobook library. My scheduled task settings look like this:

2020-07-16_13-54

You can also run these tasks manually:

2020-07-16_13-59

My settings are default (like on your screenshot), except backup directory. I’ll try run db opitimization manually, but I doubt it will help, because this is a scheduled task…
Upd: plus 2 GB in database size after PMS restart, this is insane

Are you perhaps utilizing the 3rd-party Python module “plexapi”?

No, I don’t., only official apps and Plex image for Raspberry Pi.
Well, I’ve freed 8 GB of space before PMS restart and run optimization, now it’s only 4 GB free, db size is 354 GB.

Jul 16, 2020 16:02:27.746 [0x7f745150a700] DEBUG - Activity: updated activity bf90d044-8956-424c-af61-f1879fbc9599 - completed 40.0% - Optimizing database
Jul 16, 2020 16:02:42.365 [0x7f745150a700] ERROR - SQLITE3:(nil), 13, statement aborts at 6: [DELETE FROM ‘main’.‘fts4_metadata_titles_docsize’ WHERE docid = ?] database or disk is full
Jul 16, 2020 16:02:42.388 [0x7f745150a700] ERROR - SQLITE3:(nil), 13, statement aborts at 10: [delete from fts4_metadata_titles] database or disk is full
Jul 16, 2020 16:02:42.422 [0x7f745150a700] ERROR - Exception inside transaction (inside=1) (…/Library/FullTextSearch.cpp:17): sqlite3_statement_backend::loadOne: database or disk is full
Jul 16, 2020 16:02:42.455 [0x7f745150a700] ERROR - SQLITE3:(nil), 1, statement aborts at 1: [ROLLBACK] cannot rollback - no transaction is active
Jul 16, 2020 16:02:42.455 [0x7f745150a700] ERROR - Failed to roll back transaction (…/Library/FullTextSearch.cpp:13): Cannot rollback transaction. cannot rollback - no transaction is active
Jul 16, 2020 16:02:42.564 [0x7f745150a700] ERROR - Thread: Uncaught exception running async task which was spawned by thread 0x7f747caf5700: sqlite3_statement_backend::loadOne: database or disk is full
Jul 16, 2020 16:02:42.564 [0x7f745150a700] DEBUG - Activity: Ended activity bf90d044-8956-424c-af61-f1879fbc9599.

Well, db is 364 GB after this

Of course. There is no way to create an optimized copy of a 354GB database when only 8 GB are free on the disk.
You need to perform a “repair” procedure on a Linux system with a bit more disk space.
https://support.plex.tv/articles/201100678-repair-a-corrupt-database/

So without hours of copying db to another HDD and then repairing db (btw, are you sure I need this, Plex works rather fine without any errors, when there are some space on system HDD?) I cannot do anything with it?

The question is, for how long will it continue to do so?
A database which is never optimized is going to perform slower and slower. And at some point will get damaged.
And then you’ll lose the database in the worst case and have to rebuild it from the ground up.

If I may augment here?

  1. The database is damaged.
  2. It will fail.
  3. Without remedy now, all data will be lost.

Viable option at this point:

  1. Extract the watch history
  2. Recreate fresh
  3. Import watch history.
  4. Verify database maintenance tasks enabled and system running during scheduled maintenance time.

Thanks for answer. What is “watch history” and how to export/import it?
And one more thing, recreation of my music library is quite impossible, I spent hours on it because modern Plex Music libraries work awfully with non-English artists.

How many (approx item count) of each type of media are indexed?

Series/Television
Movies
Music
Photos

EDIT Secondary question: When was the last automatic system database backup?

Series/Television — 1600
Movies — 310
Music — 6094
Photos — 8700

Backups were every 3 days, until I deleted them due to storage outage. Library was about 170 GB and I thought it’s normal, so I’ve changed backup directory to external drive. But after this db’s size doubled and Plex didn’t backup to external drive (not enough time passed, I think).

You’ve had a problem for a long time. Here is how it should look.

[/share/CACHEDEV3_DATA/.qpkg/PlexMediaServer/Library/Plex Media Server/Plug-in Support/Databases] # cd /share/CACHEDEV2_DATA/                                                
[/share/CACHEDEV2_DATA] # find qa tv* movie* uhd *music* -type f -print | wc -l                    
145450
[/share/CACHEDEV2_DATA] # cd /share/CA*/.qpkg/Ple*/Li*/Ple*/Plug*/Datab*
[/share/CACHEDEV3_DATA/.qpkg/PlexMediaServer/Library/Plex Media Server/Plug-in Support/Databases] # ls -la
total 1258272
drwxr-xr-x 2 admin administrators      4096 2020-07-16 10:46 ./
drwxr-xr-x 7 admin administrators      4096 2020-06-29 20:34 ../
-rw-r--r-- 1 admin administrators   9061376 2020-07-16 02:01 com.plexapp.plugins.library.blobs.db
-rw-r--r-- 1 admin administrators   9168896 2020-07-07 02:07 com.plexapp.plugins.library.blobs.db-2020-07-07
-rw-r--r-- 1 admin administrators   9061376 2020-07-10 02:04 com.plexapp.plugins.library.blobs.db-2020-07-10
-rw-r--r-- 1 admin administrators   9061376 2020-07-16 02:01 com.plexapp.plugins.library.blobs.db-2020-07-16
-rw-r--r-- 1 admin administrators 311670784 2020-07-16 10:46 com.plexapp.plugins.library.db
-rw-r--r-- 1 admin administrators 317048832 2020-07-07 02:07 com.plexapp.plugins.library.db-2020-07-07
-rw-r--r-- 1 admin administrators 311676928 2020-07-10 02:04 com.plexapp.plugins.library.db-2020-07-10
-rw-r--r-- 1 admin administrators 311669760 2020-07-16 02:01 com.plexapp.plugins.library.db-2020-07-16
[/share/CACHEDEV3_DATA/.qpkg/PlexMediaServer/Library/Plex Media Server/Plug-in Support/Databases] # 

145,540 items indexed in a little over 300 MB

This isn’t going to go well. You’ll need to burn it down and start again.

Brilliant. No way to backup particular libraries?

Only if you understand the database schema.
I’ve been working with it for several years now. I can pull and manipulate specific tables but extracting certain types of records (music), and their corresponding records from other tables, requires programming above my skill level.

Before going through this all again, the “Why” part must be answered.
There was an event, or several events (power off/failure) which caused this. Those need be prevented (a UPS)

Yeah, my server had several poweroffs due to overheating week or two ago.
Plex definitely must have instruments for such cases in future. Integrity checks on startup, more backup possibilities, etc.
It’s very sad that I really should “burn it down” now.

Respectfully, maybe you should consider running Plex on adequate hardware instead. An entry-class mobile phone CPU is just not up to the task, period.

What? Intel Xeon E3-1265L V2, 16 GB RAM and 512 GB HDD for system are not enough?