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?

See Also:

• Deleting cases
• Deleting old cases
• Deleting old attachments

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

If you really want to completely delete the attachment, you will need to clear out the email event as well. Only do this if you don't need the attachment or the body of the email it's in: http://fogbugz.stackexchange.com/questions/1728/moving-to-ms-sql-server-express-need-to-reduce-database-size-by-deleting-email-a/1752#1752

See also Deleting old non-email attachments, and deleting cases.

Answer 2

Hi! I'm a little bit confused. I'm using FogBugz Version 6.1.44 (DB 632, Build 369) with a MSSQL 2005 Express. Will the script work with this setup and delete old Mail-Attachments? I only wan't to delete, recovering is not so important.