How to find the total size of Attachments per space
Platform Notice: Cloud and Data Center - This article applies equally to both cloud and data center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
If you are trying to identify where the majority of your attachments are located by space, this can be helpful when trying to audit attachments to reduce the size of a space export or even a backup with attachments.
Solution
Run SQL query on the Confluence database:
select sum(cp.LONGVAL), s.spacename from spaces s INNER JOIN
content c ON c.spaceid = s.spaceid INNER JOIN contentproperties cp
ON cp.contentid = c.contentid where c.contenttype = 'ATTACHMENT' and
cp.propertyname = 'FILESIZE' GROUP BY s.spacename;Note: The number returned is in bytes. To convert the returned byte total to gigabytes, divide the result by 1073741824 (1024³).
This query sums the FILESIZE property recorded in CONTENTPROPERTIES for every attachment row in the space, including older versions of attachments and attachments on trashed pages that Confluence retains by default. As a result, the total may be larger than what you see reflected in the current, latest-version-only view of a space. To query only the latest version of each attachment, add AND c.prevver IS NULL AND c.content_status = 'current' to the WHERE clause.
Was this helpful?