How to find the total size of all attachments in Confluence

Summary

For any number of reasons (low on disk space, getting ready to migrate to a new server, simple curiosity) you might want to get the total size of attachments uploaded to Confluence.

Solution

Confluence 5.7.x and above (tested on PostgreSQL and MSSQL)

• Size of all attachments:

  1 2 3 4 5 6 7 SELECT sum(LONGVAL) AS size_bytes FROM CONTENTPROPERTIES WHERE CONTENTID IN (SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'ATTACHMENT') AND PROPERTYNAME = 'FILESIZE' ORDER BY sum(LONGVAL) DESC;

  Group by spaces:

  1 2 3 4 5 6 7 8 9 10 11 12 SELECT s.SPACEID, s.SPACENAME, sum(LONGVAL) AS size_bytes FROM CONTENTPROPERTIES c JOIN CONTENT co ON c.CONTENTID = co.CONTENTID JOIN SPACES s ON co.SPACEID = s.SPACEID WHERE c.CONTENTID IN (SELECT co.CONTENTID FROM CONTENT WHERE co.CONTENTTYPE = 'ATTACHMENT') AND c.PROPERTYNAME = 'FILESIZE' GROUP BY s.SPACENAME, s.SPACEID ORDER BY sum(LONGVAL) DESC;

  From a specific space:

  1 2 3 4 5 6 7 8 9 10 SELECT sum(LONGVAL) AS size_bytes FROM CONTENTPROPERTIES c JOIN CONTENT co ON c.CONTENTID = co.CONTENTID JOIN SPACES s ON co.SPACEID = s.SPACEID WHERE c.CONTENTID IN (SELECT co.CONTENTID FROM CONTENT WHERE co.CONTENTTYPE = 'ATTACHMENT') AND c.PROPERTYNAME = 'FILESIZE' AND s.SPACEKEY = '<SPACEKEY>' ORDER BY sum(LONGVAL) DESC;

  Ordered size of each one of the attachments:

  1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT s.SPACENAME AS SpaceName, co2.TITLE AS PageTitle, co.TITLE AS AttachmentName, cp.LONGVAL AS Size_Bytes FROM CONTENTPROPERTIES cp JOIN CONTENT co ON cp.CONTENTID = co.CONTENTID JOIN SPACES s ON co.SPACEID = s.SPACEID JOIN CONTENT co2 ON co.PAGEID = co2.CONTENTID WHERE cp.CONTENTID IN (SELECT co.CONTENTID FROM CONTENT WHERE co.CONTENTTYPE = 'ATTACHMENT') AND cp.PROPERTYNAME = 'FILESIZE' ORDER BY s.SPACENAME, co2.TITLE, cp.LONGVAL DESC;
  Do note, Confluence stores file size in bytes. You will need to do some division to get the total size to KB (1,000 bytes) or MB (1,000,000 bytes).

Confluence 5.6.x and below

• Size of all attachments:

  1 SELECT sum(FILESIZE) FROM ATTACHMENTS;