How to find the largest attachment files in your Confluence instance

Still need help?

The Atlassian Community is here for you.

Ask the community

Purpose

If your instance is taking too much disk space or you'd like to check your attachment sizes to enforce some restrictions, you might want to check which users or pages have the biggest attachments. For versions of Confluence 5.4 or earlier you might have attachments stored in the database, otherwise they will be stored in your <confluence_home>/attachments/ver003 folder unless you upgraded from a previous version and did not migrate the attachments over to the filesystem.

It's easy to check by going to  -> General configuration -> Attachment Storage.


Solution 

  • If your attachments are stored in the database, you can run the following query which will return the size of the attachment in bytes, the attachmentid, title, user that uploaded, and the page to which it was attached:

    SELECT octet_length(a.data) AS SIZE,
           a.attachmentid,
           c.title AS attachmentTitle,
           u.username AS guilty,
           co.title AS pageTitle
    FROM ATTACHMENTDATA AS a
    JOIN CONTENT AS c ON c.contentid = a.attachmentid
    JOIN USER_MAPPING AS u ON u.user_key = c.creator
    JOIN CONTENT AS co ON c.pageid = co.contentid
    ORDER BY SIZE DESC
  • If you are storing the attachments in the file system, you can run this slightly modified query:


    SELECT DISTINCT c.contentid,
    				c.spaceid,
                    c.title AS attachmentTitle,
                    u.username AS uploadedBy,
                    co.title AS pageTitle,
                    cn.longval AS bytes,
                    s.spacekey as spacekey,
                    s.spacename as spacename
    FROM CONTENT AS c
    JOIN USER_MAPPING AS u ON u.user_key = c.creator
    JOIN CONTENT AS co ON c.pageid = co.contentid
    JOIN spaces AS s ON c.spaceid=s.spaceid
    JOIN CONTENTPROPERTIES AS cn ON cn.contentid = c.contentid
    WHERE c.contenttype = 'ATTACHMENT'
      AND cn.longval IS NOT NULL
    ORDER BY cn.longval DESC;

Last modified on Oct 21, 2020

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.