Determining trash file size

Is there a way to determine how many documents or what the file size is for all of the documents that are in the trash in confluence? For an individual space or the entire instance?

I have been doing a lot of cleanup of attachments recently and I know when a document is deleted - it doesn't actually get removed off the server until the trash is purged. I am trying to see if there is a way to determine how much space the trash is taking up so I can decide if I want to purge or not.

Thanks.

2 answers

1 accepted

4 votes

Answer accepted

This is a query I wrote up a while ago to get the file paths of attachments. I edited it to just pull back the deleted files. The first column would be the file paths for all your deleted files. You could probably use that to get the disk space for all your deleted attachments. Below are the MSSQL and MySQL versions. If you are on PostgreSQL you might need to edit the syntax a bit.

MSSQL

select
    '/ver003'
    + '/' + cast(right(SPACEID,3) % 250 as varchar(10))
    + '/' + cast(left(right(SPACEID,6),3) % 250 as varchar(10))
    + '/' + cast(SPACEID as varchar(10))
    + '/' + cast(right(PAGEID,3) % 250 as varchar(10))
    + '/' + cast(left(right(PAGEID,6),3) % 250 as varchar(10))
    + '/' + cast(PAGEID as varchar(10))
    + '/' + case when PREVVER is null then cast(CONTENTID as varchar(10)) else cast(PREVVER as varchar(10)) end
    + '/' + cast([VERSION] as varchar(10)) as FILEPATH,
    *
from [dbo].[CONTENT]
where
    CONTENTTYPE = 'ATTACHMENT'
    and CONTENT_STATUS = 'deleted'

MySQL

select
    replace(concat('/ver003'
    , '/' , cast(right(SPACEID,3) % 250 as char(10))
    , '/' , cast(left(right(SPACEID,6),3) % 250 as char(10))
    , '/' , cast(SPACEID as char(10))
    , '/' , cast(right(PAGEID,3) % 250 as char(10))
    , '/' , cast(left(right(PAGEID,6),3) % 250 as char(10))
    , '/' , cast(PAGEID as char(10))
    , '/' , case when PREVVER is null then cast(CONTENTID as char(10)) else cast(PREVVER as char(10)) end
    , '/' , cast(`VERSION` as char(10))), ' ', '') as FILEPATH,
    CONTENT.*
from CONTENT
where
    CONTENTTYPE = 'ATTACHMENT'
    and CONTENT_STATUS = 'deleted'

PostgreSQL

select
    '/ver003'
    || '/' || cast(right(SPACEID,3) % 250 as varchar(10))
    || '/' || cast(left(right(SPACEID,6),3) % 250 as varchar(10))
    || '/' || cast(SPACEID as varchar(10))
    || '/' || cast(right(PAGEID,3) % 250 as varchar(10))
    || '/' || cast(left(right(PAGEID,6),3) % 250 as varchar(10))
    || '/' || cast(PAGEID as varchar(10))
    || '/' || case when PREVVER is null then cast(CONTENTID as varchar(10)) else cast(PREVVER as varchar(10)) end
    || '/' || cast(VERSION as varchar(10)) as FILEPATH,
    *
from CONTENT
where
    CONTENTTYPE = 'ATTACHMENT'
    and CONTENT_STATUS = 'deleted'