Skip to content

Instantly share code, notes, and snippets.

@ragingcomputer
Last active November 27, 2023 03:47
Show Gist options
  • Save ragingcomputer/8a0c3a043489d7df5f0821b1faf7142a to your computer and use it in GitHub Desktop.
Save ragingcomputer/8a0c3a043489d7df5f0821b1faf7142a to your computer and use it in GitHub Desktop.
SQL query for Moodle to show space usage in Course Backup Area by course
SELECT mc.fullname AS CourseName,
ROUND((SUM(mf.filesize)/1024)/1024, 2) AS Storage_Used_MB
FROM mdl_files AS mf
LEFT JOIN mdl_context AS ctx ON mf.contextid = ctx.id
LEFT JOIN mdl_course AS mc ON ctx.instanceid = mc.id
WHERE mf.component = 'backup'
AND mf.filearea = 'course'
AND NOT mf.filename = '.'
GROUP BY mf.contextid
ORDER BY Storage_Used_MB DESC
@rajveer-web
Copy link

SELECT
c.id AS course_id,
c.fullname AS course_name,
SUM(f.filesize) AS total_backup_space
FROM
mdl_course c
JOIN
mdl_files f ON c.id = f.contextid
JOIN
mdl_backup_courses bc ON c.id = bc.courseid
WHERE
f.component = 'backup'
GROUP BY
c.id, c.fullname
ORDER BY
total_backup_space DESC;

calculates the total backup space used by each course by summing up the file sizes from the mdl_files table where the component is 'backup.' It provides a list of courses along with their corresponding total backup space, ordered in descending order.

Additionally, always back up your database before running any queries.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment