Question

I have a bunch of old customer emails and attachments that I want to get rid of. How can I get rid of large email attachments that are older than 6 months old?

Answer 1

First, make sure to back up your database prior to running the following script. :)

The following query should delete any email attachments that are more than 6 months old.

DELETE FROM Attachment WHERE ixAttachment IN
    (SELECT Attachment.ixAttachment
            FROM Attachment INNER JOIN BugEvent
                ON Attachment.ixBugEvent = BugEvent.ixBugEvent
            WHERE DATEDIFF(mm, dt, GetDate()) > 6 AND fEmail = 1
    )

Or for MySQL

DELETE FROM Attachment WHERE ixAttachment IN
    (SELECT Attachment.ixAttachment
            FROM Attachment INNER JOIN BugEvent
                ON Attachment.ixBugEvent = BugEvent.ixBugEvent
            WHERE dt < DATE_SUB(NOW(), INTERVAL 6 MONTH) > 6 AND fEmail = 1
    )

If at some point you need to retrieve the deleted attachments for a specific bug, you should be able to do so by running the following query and then re-visiting that case (replace CASE_ID with the id of the bug whose attachments you're trying to recover):

UPDATE BugEvent SET fHTML = 0 WHERE ixBug = CASE_ID