DBRepair development

[ChuckPa]

DBRepair v 1.11.01

Special release

Due to problems with PlexMediaServer, I’ve made some special changes to DBRepair to help keep control and cleanup.

1. Database delete action now encased in Transaction/Commit for speed
2. Space available calculation based on actual Databases directory location
3. Menu change (much needed)
   – Thanks to @bslatyer (Braeden) · GitHub
   98 = QUIT
   99 = EXIT

This is DEVELOPER TEST but didn’t crash my DB’s when I ran it.

ChuckPa-DBRepair-v1.11.01.tar (160 KB)

When we confirm this is working for MacOS and Linux, I’ll release it to everyone via normal GitHub process.

[nibsuk]

Hi @ChuckPa - thanks for your script. I found it via a Reddit post & started to run it (Windows ps1 version 1.01.01) as my database had filled up my drive overnight

I only just got around to reading the Plex forum, specifically this one:

Library.db size more than doubled in latest version

I did release 1.10.06 of my (not Plex’s) repair tool. It will reduce the size. I did not write it to be fast however it did reduce a large DB in about 20 minutes on a little DS918 NAS.

Where it states that the AUTO function might need 3 x the database size to run - I don’t have that much free space

What are my options now that the script is running? Can I stop it or is that going to cause problems?

Any help is much appreciated

[ChuckPa]

ALL:

A lot of information has come to light for me to use.

I have more info on how to reduce the DB size safely without needing the 3x space requirement.

Please give me a bit here and then I’ll post an update in this thread.

[DDxPlague]

Now I understand why my DB went from <3GB in size a few weeks ago to 100+.

Do we know if the bug that caused the explosion in size was fixed?

[nibsuk]

They say it is fixed in the latest beta build

Plex Media Server

Plex Media Server 1.41.8.9834 is now available to Plex Pass users in the Beta update channel. FIXES: (Bandwidth) Transcode request bandwidth was not being reported (PM-3430) (Database) Some database clean up operations could fail during server start up (PM-3542) (GPU) Selected GPU may not have persisted across system reboots on windows (PM-3258) (Performance) Improved some database queries that could cause long load times for movie detail pages (PM-2390) (Transcoder) AV1 decode would fail on …

[ChuckPa]

There’s one thing that doesn’t address – A full disk where PMS can’t start.

I’ve finished my development, Now to clean it up

This is 31 GB database down to 216 MB in 13 minutes.

[chuck@lizum databases.2025.05.17.2109]$ time sudo ./dbr.sh --sqlite "/usr/lib/plexmediaserver" --databases "$(pwd)" deflate
 
 
 
      Database Repair Utility for Plex Media Server  (User Defined)
                       Version v1.11.02
 
[2025-05-30 15.09.22]       PlexSQLite = '/usr/lib/plexmediaserver/Plex SQLite'
[2025-05-30 15.09.22]       Databases  = '/usb/plex/PMS-save/Plug-in Support/Databases/databases.2025.05.17'
 
[2025-05-30 15.09.22] This command operates directly on your existing PMS DB.
[2025-05-30 15.09.22] There is no safeguard.
[2025-05-30 15.09.22] Please ensure your databases are NOT corrupted -or- you have a full backup
[2025-05-30 15.09.22] DO NOT INTERRUPT once started.
[2025-05-30 15.09.22]  
PMS is shutdown
Result=off
Construct SQL command
REMOVING 1
[2025-05-30 15.09.22] Removing - Pass 1
REMOVING 2
[2025-05-30 15.11.46] Removing - Pass 2
REMOVING 3
[2025-05-30 15.14.07] Removing - Pass 3
REMOVING 4
[2025-05-30 15.16.24] Removing - Pass 4
REMOVING 5
[2025-05-30 15.18.52] Removing - Pass 5
REMOVING 6
[2025-05-30 15.21.03] Removing - Pass 6
[2025-05-30 15.22.38] Removed records.
[2025-05-30 15.22.38] Vacuuming DB to reclaim working space.
[2025-05-30 15.22.40] Current Database size = 216 MB
wal
 

real	13m18.174s
user	0m0.004s
sys	0m0.005s
[chuck@lizum databases.2025.05.17.2110]$

I will rework the wording so it’s less scary. It is safer now. Wrapped in a transaction.

[ChuckPa]

Here we are

This is the quick-fix to get you running while the official takes care of the root problem.

1. There is a new command (temporary) DEFLATE
2. It will remove 100 million records per block until done
3. It requires no additional disk space to run. (for those where PMS can’t run)
4. Data is protected by a Transaction / Commit block.

Below, 31 GB is reduced to 216 MB in 13 minutes on an i9-12900 with USB SSD

Here is my console output

[chuck@lizum databases.2025.05.17.2124]$ time sudo ./DBRepair.sh --sqlite /usr/lib/plexmediaserver --databases "$(pwd)" deflate
 
 
 
      Database Repair Utility for Plex Media Server  (User Defined)
                       Version v1.11.02
 
[2025-05-30 16.09.09]       PlexSQLite = '/usr/lib/plexmediaserver/Plex SQLite'
[2025-05-30 16.09.09]       Databases  = '/usb/plex/PMS-save/Plug-in Support/Databases/databases.2025.05.17'
 
[2025-05-30 16.09.09] This command operates directly on your existing PMS DB.
[2025-05-30 16.09.09] There are no backups however the DB is protected by transaction blocking.
[2025-05-30 16.09.09] DO NOT INTERRUPT once started but you can if you must (It will resume where it left off).
[2025-05-30 16.09.09]  
[2025-05-30 16.09.09] Removing - block 1
[2025-05-30 16.11.37] Removing - block 2
[2025-05-30 16.14.03] Removing - block 3
[2025-05-30 16.16.23] Removing - block 4
[2025-05-30 16.18.52] Removing - block 5
[2025-05-30 16.21.05] Removing - block 6
[2025-05-30 16.22.40] Removed 571458809 records.
[2025-05-30 16.22.40] Vacuuming DB to reclaim working space.
[2025-05-30 16.22.43] Initial Database size = 33120 MB
[2025-05-30 16.22.43] Final Database size   = 216 MB
 

real	13m34.032s
user	0m0.003s
sys	0m0.006s
[chuck@lizum databases.2025.05.17.2125]$ 

Here is the tar ball. This will go to GitHub as soon as there is confirmed confidence.

ChuckPa-DBRepair-v1.11.02.tar (160 KB)

Please let me know your results.

[DDxPlague]

Trying to decide if its worth cancelling my in-progress 8+hr run on 1.11.01 to see if this shrinks my 100GB DB faster.

Any problems cancelling an in-process run?

[DDxPlague]

No dice:

Looks like pidof doesn’t exist on MacOS, and then it locked it so when I try to re-run it it also fails.

 Databases sudo ./DBRepair.sh --sqlite /Applications/Plex\ Media\ Server.app/Contents/MacOS/Plex\ SQLite --databases "$(pwd)" deflate


stat: illegal option -- c
usage: stat [-FLnq] [-f format | -l | -r | -s | -x] [-t timefmt] [file ...]
stat: illegal option -- c
usage: stat [-FLnq] [-f format | -l | -r | -s | -x] [-t timefmt] [file ...]


      Database Repair Utility for Plex Media Server  (User Defined)
                       Version v1.11.02

[2025-05-30 15.26.12]       PlexSQLite = '/Applications/Plex Media Server.app/Contents/MacOS/Plex SQLite'
[2025-05-30 15.26.12]       Databases  = '/Volumes/NVMe_Workspace/Plex Application Support/Plex Media Server/Plug-in Support/Databases'

[2025-05-30 15.26.12] This command operates directly on your existing PMS DB.
[2025-05-30 15.26.12] There are no backups however the DB is protected by transaction blocking.
[2025-05-30 15.26.12] DO NOT INTERRUPT once started but you can if you must (It will resume where it left off).
[2025-05-30 15.26.12]
./DBRepair.sh: line 1545: pidof: command not found
Error: in prepare, database is locked (5)
stat: illegal option -- c
usage: stat [-FLnq] [-f format | -l | -r | -s | -x] [-t timefmt] [file ...]
[2025-05-30 15.26.12] Removing - block 1
Error: in prepare, database is locked (5)
./DBRepair.sh: line 1776: 0 + : syntax error: operand expected (error token is " ")

[DDxPlague]

did a

brew install pidof

And then killed the process that was still running in the background and looks like we’re moving now. I’ll update when I have info for you.

[DDxPlague]

Looks like it’s hung at the moment. Been 15+ minutes with no output:

stat: illegal option -- c
usage: stat [-FLnq] [-f format | -l | -r | -s | -x] [-t timefmt] [file ...]
stat: illegal option -- c
usage: stat [-FLnq] [-f format | -l | -r | -s | -x] [-t timefmt] [file ...]


      Database Repair Utility for Plex Media Server  (User Defined)
                       Version v1.11.02

[2025-05-30 15.29.51]       PlexSQLite = '/Applications/Plex Media Server.app/Contents/MacOS/Plex SQLite'
[2025-05-30 15.29.51]       Databases  = '/Volumes/NVMe_Workspace/Plex Application Support/Plex Media Server/Plug-in Support/Databases'

[2025-05-30 15.29.51] This command operates directly on your existing PMS DB.
[2025-05-30 15.29.51] There are no backups however the DB is protected by transaction blocking.
[2025-05-30 15.29.51] DO NOT INTERRUPT once started but you can if you must (It will resume where it left off).
[2025-05-30 15.29.51]

Activity monitor shows the SQLite still reading/writing data so going to leave it be for now and check back.

[ChuckPa]

@DDxPlague

You’re running on MacOS.

When Manual Mode is invoked, no host detection is performed.
In Manual Mode, all commands are based on Linux, not Unix (MacOS).

If you run the tool normally:

1. It will detect MacOS and configure commands for the MacOS host
2. It will still compute free available based on your suggestion / PR to use $DBDIR. This means it will resolve through your symlink to where the databases are actually stored.

[DDxPlague]

Just cancelled and tried a restart with the normal way and the 911 option.

Like the number by the way.

(Also doing this all on a backed up directory so my regular one stays the same until I know it works)

[ChuckPa]

Since our discussion,

1. I added transaction protection to all database actions.
   – anything not completed is immediately undone by SQLite

2. Deflate or 911 – both work.

Look at my output.

That’s an i9 - 12900 (Dragon Canyon) NUC with NVMe SSD. It reads 1GB/sec.
The biggest load was being CPU-bound (1 core) for that 2.5 minutes per block.

Lesser CPU will take longer

[FordGuy61]

FYI, I ran DEFLATE on my Synology DS918+ using the same database as Chuck.

This is with a Celeron J3455 CPU and hard drives. Cleaned the database in about 1 hour 40 minutes.

So not quite the 13 minutes with an i9-12900, but still much faster than the original version.

[2025-05-30 15.48.31]
[2025-05-30 15.48.32] Removing - block 1
[2025-05-30 16.03.49] Removing - block 2
[2025-05-30 16.19.02] Removing - block 3
[2025-05-30 16.36.19] Removing - block 4
[2025-05-30 16.53.51] Removing - block 5
[2025-05-30 17.09.36] Removing - block 6
[2025-05-30 17.24.12] Removed 571458809 records.
[2025-05-30 17.24.12] Vacuuming DB to reclaim working space.
[2025-05-30 17.28.16] Initial Database size = 33120 MB
[2025-05-30 17.28.16] Final Database size   = 216 MB

[DDxPlague]

Looks like some sort of bug on macOS for the output, but the new DB is working and much much smaller. Took about an hour.

Ok to begin deflating the databases? (Y/N) ? y
Removing - block 1
Removed 0 records.
Vacuuming DB to reclaim working space.
Initial Database size = 107368 MB
Final Database size   = 469 MB

Trying to turn the server back on now…

And it works! Thanks for this. Way way better.

[ChuckPa]

Something isn’t right there. Very odd that the count didn’t return.
( I know it did get a value )

Check me please on this?

  Count=1
  while [ $Removed -eq $Limit ]
  do
    Output "Removing - block $Count"
    Removed=$("$PLEX_SQLITE" "$CPPL.db" "$SQL" | tail -1)
    TotalRemoved=$(($TotalRemoved + $Removed))
    Count=$((Count+1))
  done

  Output "Removed $TotalRemoved records."
  Output "Vacuuming DB to reclaim working space."
  "$PLEX_SQLITE" "$CPPL.db" "vacuum;"

[ChuckPa]

This is the output from what’s now on my branch .

[chuck@lizum databases.2025.05.17.2005]$ sudo time ~/git/chuck/DBRepair/DBRepair.sh --sqlite /usr/lib/plexmediaserver --databases "$(pwd)" deflate
 
 
 
      Database Repair Utility for Plex Media Server  (User Defined)
                       Version v1.11.02
 
[2025-05-30 19.26.59]       PlexSQLite = '/usr/lib/plexmediaserver/Plex SQLite'
[2025-05-30 19.26.59]       Databases  = '/usb/plex/PMS-save/Plug-in Support/Databases/databases.2025.05.17'
 
[2025-05-30 19.26.59] This command operates directly on your existing PMS DB.
[2025-05-30 19.26.59] There are no backups however the DB is protected by transaction blocking.
[2025-05-30 19.26.59] DO NOT INTERRUPT once started but you can if you must (It will resume where it left off).
[2025-05-30 19.26.59]  
[2025-05-30 19.26.59] Removing - block 1
[2025-05-30 19.29.33] Removing - block 2
[2025-05-30 19.32.01] Removing - block 3
[2025-05-30 19.34.23] Removing - block 4
[2025-05-30 19.36.56] Removing - block 5
[2025-05-30 19.39.09] Removing - block 6
[2025-05-30 19.40.44] Removed 571458809 records.
[2025-05-30 19.40.44] Vacuuming DB to reclaim working space.
[2025-05-30 19.40.47] Initial Database size = 33120 MB
[2025-05-30 19.40.47] Final Database size   = 216 MB
 
737.98user 32.98system 13:47.81elapsed 93%CPU (0avgtext+0avgdata 37384maxresident)k
52100992inputs+89108072outputs (0major+1756763minor)pagefaults 0swaps
[chuck@lizum databases.2025.05.17.2006]$ 

Now to figure out if I messed up the shell math syntax on MacOS

[ChuckPa]

For everyone on Linux platforms –

v1.11.02 is now live.

( I will update for MacOS when we figure out what is happening with the shell math )

It works. It just doesn’t report as nicely as Linux (cough - cough)

[jfreiman]

@ChuckPa

v1.11.02 is now live.

( I will update for MacOS when we figure out what is happening with the shell math )

Does the new DEFLATE command work for Windows Builds?