TYPO3 scheduler task to delete rows from sys_file without 1) a corresponding file and 2) a reference in sys_file_reference

RemoveUnreferencedFilesMarkedAsMissing.php

<?php
namespace My\Ext\Scheduler\Task;

/**
 * This file is part of the TYPO3 CMS project.
 *
 * It is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License, either version 2
 * of the License, or any later version.
 *
 * For the full copyright and license information, please read the
 * LICENSE.txt file that was distributed with this source code.
 *
 * The TYPO3 project - inspiring people to share!
 */

use TYPO3\CMS\Core\Messaging\FlashMessage;
use TYPO3\CMS\Core\Messaging\FlashMessageService;
use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Scheduler\Task\AbstractTask;

/**
 * TYPO3 scheduler task to delete rows from sys_file without 1) a corresponding file and 2) a reference in sys_file_reference
 */
class RemoveUnreferencedFilesMarkedAsMissing extends AbstractTask
{

    /**
     * Function executed from the Scheduler.
     *
     * @return boolean
     */
    public function execute()
    {
        $this->getDatabaseConnection()->exec_DELETEquery(
            'sys_file',
            'sys_file.missing=1 AND sys_file.uid NOT IN (SELECT sys_file_reference.uid_local FROM sys_file_reference WHERE sys_file_reference.uid_local=sys_file.uid)'
        );
        if ($this->getDatabaseConnection()->sql_error() === '') {
            $this->showFlashMessage(
                sprintf(
                    'Successfully deleted %d sys_file records.',
                    $this->getDatabaseConnection()->sql_affected_rows()
                )
            );
        } else {
            $this->showFlashMessage(
                sprintf(
                    'Failed to delete sys_file records. Error: ',
                    $this->getDatabaseConnection()->sql_error()
                )
            );
        }

        return true;
    }

    /**
     * This method returns additional information
     *
     * @return string Task description to display
     */
    public function getAdditionalInformation()
    {
        $description = 'Everything clean, no action required!';

        $missingFiles = $this->getDatabaseConnection()->exec_SELECTcountRows(
            '*',
            'sys_file',
            'sys_file.missing=1 AND sys_file.uid NOT IN (SELECT sys_file_reference.uid_local FROM sys_file_reference WHERE sys_file_reference.uid_local=sys_file.uid)'
        );

        if ($missingFiles > 0) {
            $description = sprintf(
                '%d sys_file records are marked as missing, have no reference in sys_file_reference and can be deleted.',
                $missingFiles
            );
        }

        return $description;
    }

    /**
     * Displays a flashMessage in the backend
     *
     * @param string $description
     * @return void
     */
    protected function showFlashMessage($description)
    {
        $message = GeneralUtility::makeInstance(FlashMessage::class,
            $description,
            'Delete unreferenced files marked as missing',
            FlashMessage::INFO,
            true
        );
        $flashMessageService = GeneralUtility::makeInstance(FlashMessageService::class);
        $defaultFlashMessageQueue = $flashMessageService->getMessageQueueByIdentifier();
        $defaultFlashMessageQueue->enqueue($message);
    }

    /**
     * getDatabaseConnection
     *
     * @return \TYPO3\CMS\Core\Database\DatabaseConnection
     */
    protected function getDatabaseConnection()
    {
        return $GLOBALS['TYPO3_DB'];
    }

}

Thanks for providing this task class!
I added a linked to it in https://forge.typo3.org/issues/59312

Thanks @stmllr!

Line 49 is missing a substitution placeholder for the error message. The line should be adjusted like so: 'Failed to delete sys_file records. Error: %s'