Server Version#: 1.43.0.10389
Player Version#: 4.156.0
The Plex Database folder on my PC is taking up a ton of room. There are a number of old files, should Plex be deleting these? Can I delete them without issue?
I’m not a Windows guy. I am on the Linux side. I have a script which handles this for Linux. It deflates a bloated DB back to normal size
How good are you with the Windows command line ?
I ask because the Windows version of DBRepair doesn’t have the Deflate command yet (The windows developer who helps me is on business travel)
the ones with the dates are database back ups in case something goes wrong.
The setting to make the back ups is in the Scheduled Tasks settings of the server
2 Likes
@ChuckPa if you need help with the windows version I can help if we can get a copy of an affected database.
1 Like
How about a stand-alone script which does Deflate ?
Sure.
I’m not great but could follow a walkthrough probably
I’ve chatted with Dbirch in private.
We’re going to try and create a Windows version (BAT file) which will deflate your database for you.
I sent him the specific code from DBRepair and a sample bloated DB.
I’d like to give him a chance to work through this and see what we come up with.
If it all goes as planned (not too difficult), we’ll have a BAT script which will do the job for you.
Caution in advance, deflating a bloated database takes quite a bit of time.
The reason for this is because SQLite must go through each record, one at a time, finding out what it has to get rid of.
I have an i9 CPU. The 31 GB test database took almost 20 minutes to complete.
I’ve seen it take a few hours on some machines. When it was done, the DB was back it its normal 280 MB size.
It doesn’t produce interim output, but it won’t hang.
Patience will be required when doing this.
1 Like
My version of the .bat is only missing to set the primary key of the copied table. (IIRC, that is what was missing in the original Linux script as well)
Sounds good! Thanks so much, I can definitely be patient.
Thanks for this.
I added the primary key part and will now start the testing / tweaking.
Looking forward to trying this myself as my database has grown to 128gb
Dbirch found one typo on my part.
I’m waiting for him to confirm it’s fixed and the resultant DB is valid.
Windows Folks,
Dbirch has tested & confirmed we’re good to go with his DBs.
For those with large DBs, it will take a long time to slug through the DB.
It will not hang.
DO NOT INTERRUPT IT.
Dependent on CPU and HDD/SSD, it might take hours.
Unfortunately Plex’s SQLite has no output while processing.
For those who use use this, I recommend making a backup of your DBs prior to running it.
Please provide feedback .
Deflate-Win.zip (2.1 KB)
1 Like
Posting the script for copy & paste, avoiding potential deletion of the above file attachment by forum purge.
@echo off
setlocal EnableDelayedExpansion
REM Windows .BAT script to deflate Plex Media Server database
REM Run this script under the same Windows user account that you use to run Plex Media Server
REM Find PMS installation location.
for /F "tokens=2* skip=2" %%a in ('REG.EXE QUERY "HKCU\Software\Plex, Inc.\Plex Media Server" /v "InstallFolder" 2^> nul') do set "PSQL=%%b\Plex SQLite.exe"
if not exist "%PSQL%" (
REM InstallFolder might be set under HKLM, not HKCU
for /F "tokens=2* skip=2" %%a in ('REG.EXE QUERY "HKLM\Software\Plex, Inc.\Plex Media Server" /v "InstallFolder" 2^> nul') do set "PSQL=%%b\Plex SQLite.exe"
)
REM If InstallFolder wasn't set, or the resulting file doesn't exist, iterate through the
REM PROGRAMFILES variables looking for it. If we still can't find it, ask the user to provide it.
if not exist "%PSQL%" (
if exist "%PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe" (
set "PSQL=%PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe"
) else (
if exist "%PROGRAMFILES(X86)%\Plex\Plex Media Server\Plex SQLite.exe" (
echo NOTE: 32-bit version of PMS detected on a 64-bit version of Windows. Updating to the 64-bit release of PMS is recommended.
set "PSQL=%PROGRAMFILES(X86)%\Plex\Plex Media Server\Plex SQLite.exe"
) else (
echo Could not determine SQLite path. Please provide it below
echo Normally %PROGRAMFILES%\Plex\Plex Media Server\Plex SQLite.exe
echo.
REM Last ditch effort, ask the user for the full path to Plex SQLite.exe
set /p "PSQL=Path to Plex SQLite.exe: "
if not exist "!PSQL!" (
echo "!PSQL!" could not be found. Cannot continue.
goto :EOF
)
)
)
)
REM Find PMS database location
for /F "tokens=2* skip=2" %%a in ('REG.EXE QUERY "HKCU\Software\Plex, Inc.\Plex Media Server" /v "LocalAppDataPath" 2^> nul') do set "DBDIR=%%b\Plex Media Server\Plug-in Support\Databases"
if not exist "%DBDIR%" (
if exist "%LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases" (
set "DBDIR=%LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases"
) else (
echo Could not determine Plex database path.
echo Normally %LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases
echo.
goto :EOF
)
)
set "CPPL=com.plexapp.plugins.library.db"
REM Sanity checks
if not exist "%DBDIR%" (
echo ERROR: No such directory '%DBDIR%'
exit /b 1
)
if not exist "%PSQL%" (
echo ERROR: Cannot find 'Plex SQLite' at '%PSQL%'
exit /b 2
)
if not exist "%DBDIR%\%CPPL%" (
echo ERROR: Cannot find or no write permission for '%CPPL%'
exit /b 3
)
echo ==== Current listing
dir "%DBDIR%"
REM Change to the databases directory
cd /d "%DBDIR%"
REM Remove output if exists
if exist "new.com.plexapp.plugins.library.db" del /f "new.com.plexapp.plugins.library.db"
echo -- Deflating into new DB
echo DROP TABLE IF EXISTS temp_bandwidth; > temp.sql
echo CREATE TABLE temp_bandwidth ( >> temp.sql
echo id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, >> temp.sql
echo account_id INTEGER, >> temp.sql
echo device_id INTEGER, >> temp.sql
echo timespan INTEGER, >> temp.sql
echo at INTEGER, >> temp.sql
echo lan INTEGER, >> temp.sql
echo bytes INTEGER >> temp.sql
echo ); >> temp.sql
echo -- Copying good data from table. This takes time.
echo INSERT INTO temp_bandwidth >> temp.sql
echo ( account_id, device_id, timespan, at, lan, bytes ) >> temp.sql
echo SELECT account_id, device_id, timespan, at, COALESCE(lan, 0), bytes >> temp.sql
echo FROM statistics_bandwidth WHERE account_id not null; >> temp.sql
echo DROP TABLE statistics_bandwidth; >> temp.sql
echo ALTER TABLE temp_bandwidth RENAME to statistics_bandwidth; >> temp.sql
echo CREATE INDEX 'index_statistics_bandwidth_on_at' ON statistics_bandwidth ('at'); >> temp.sql
echo CREATE INDEX 'index_statistics_bandwidth_on_account_id_and_timespan_and_at' ON 'statistics_bandwidth' ('account_id', 'timespan', 'at'); >> temp.sql
echo VACUUM main into './new.com.plexapp.plugins.library.db'; >> temp.sql
echo This step will take time. Please do not interrupt.
"%PSQL%" "%CPPL%" < temp.sql
set Result=%ERRORLEVEL%
REM Clean up temporary SQL file
del /f temp.sql
REM Check for errors
if %Result% NEQ 0 (
echo ERROR: Error code %Result% from deflate. Seek assistance
exit /b 4
)
REM Confirm we have a DB
set OK=1
if not exist "new.com.plexapp.plugins.library.db" set OK=0
REM Confirm not zero size (using dir to get file size)
for %%F in ("new.com.plexapp.plugins.library.db") do set NewSize=%%~zF
if !NewSize! LEQ 300000 set OK=0
REM Abort if not OK
if %OK% NEQ 1 (
echo ERROR: Something went wrong. Exiting.
exit /b 31
)
REM Get file ownership and permissions (Windows equivalent)
REM Note: Windows doesn't have direct chown/chmod equivalents; using icacls for permissions info
echo ========== DEBUG ================
echo Owner: (Use icacls to check manually if needed)
icacls "%CPPL%"
echo New Size: %NewSize% bytes
REM Move existing DBs
echo Moving existing DB to '-bloat'
move "%CPPL%" "com.plexapp.plugins.library.db-bloat"
if exist "%CPPL%" move "%CPPL%" "%CPPL%-bloated"
echo Removing WAL and SHM if exist
if exist "%CPPL%-wal" del /f "%CPPL%-wal"
if exist "%CPPL%-shm" del /f "%CPPL%-shm"
REM Move new DB into position
echo Making new DB active
move "new.com.plexapp.plugins.library.db" "%CPPL%"
REM Set permissions (restore original permissions if possible)
REM Note: Windows permissions are complex; may need manual adjustment
echo Restoring permissions (check manually if needed)
icacls "%CPPL%" /reset
echo New databases directory contents
dir "%DBDIR%"
echo Done.
exit /b 0
Thank you so much for this. To ensure I don’t mess anything up a few questions:
- What’s the best way to backup my DBs? I usually just copy them over to my external hard drive, is there a better way?
- How often should I need to run this? Or is it a one time fixes all kind of solution?
- Why is this better than just deleting the old backups?
Thanks!
With PMS stopped, and making sure there are no dangling processes,
YES, copy all the files.
After you deflate the DB and confirm everything is normal,
you can delete that temporary backup
