Purging Old FogBugz Data – FogBugz

Warning: always backup your database before directly working with it and if possible, setup a test instance of FogBugz you can run this against before deleting from your production database.

If you’d like to archive old FogBugz data to save space, here are some instructions on how to do so. If you have any questions, don’t hesitate to contact us.

Contents

1 Purge Old Cases
2 Purge Old Cases for a Specific Project
3 Purge Old Case Attachments
4 Purge Old Email Attachments

Purge Old Cases

Here’s how to delete old FogBugz cases from the database. In our example, we’ll delete anything older than 6 months.

Find the Cases

SELECT Bug.ixBug,Bug.dtClosed,BugEvent.dt as dtLastUpdated
FROM BugEvent
INNER JOIN Bug
    ON Bug.ixBugEventLatest =BugEvent.ixBugEvent
WHERE DATEDIFF(mm,Bug.dtClosed,GetDate())>6
OR DATEDIFF(mm,BugEvent.dt,GetDate())>6
order by Bug.ixbug

Delete Attachments (FogBugz 8)

DELETE FROM Attachment WHERE ixAttachment IN
(SELECT AttachmentReference.ixAttachment
        FROM AttachmentReference
        INNER JOIN BugEvent
            on BugEvent.ixBugEvent =AttachmentReference.ixBugEvent
        INNER JOIN Bug
            ON Bug.ixBugEventLatest =BugEvent.ixBugEvent
        WHERE DATEDIFF(mm,Bug.dtClosed,GetDate())>6
        OR DATEDIFF(mm,BugEvent.dt,GetDate())>6)

Delete Attachment References (FogBugz 8)

DELETE FROM AttachmentReference WHERE ixAttachmentReference IN
(SELECT AttachmentReference.ixAttachmentReference
        FROM AttachmentReference
        INNER JOIN BugEvent
            on BugEvent.ixBugEvent =AttachmentReference.ixBugEvent
        INNER JOIN Bug
            ON Bug.ixBugEventLatest =BugEvent.ixBugEvent
        WHERE DATEDIFF(mm,Bug.dtClosed,GetDate())>6
        OR DATEDIFF(mm,BugEvent.dt,GetDate())>6)

Delete Attachments (FogBugz 7 Only)

DELETE FROM Attachment WHERE ixAttachment IN
(SELECT Attachment.ixAttachment
        FROM Attachment
        INNER JOIN BugEvent
            on BugEvent.ixBugEvent =Attachment.ixBugEvent
        INNER JOIN Bug
            ON Bug.ixBugEventLatest =BugEvent.ixBugEvent
        WHERE DATEDIFF(mm,Bug.dtClosed,GetDate())>6
        OR DATEDIFF(mm,BugEvent.dt,GetDate())>6)

Delete Cases

Ues the output of this select in the parentheses of each query below.

SELECT Bug.ixBug
FROM BugEvent
INNER JOIN Bug
    ON Bug.ixBugEventLatest =BugEvent.ixBugEvent
WHERE DATEDIFF(mm,Bug.dtClosed,GetDate())>6
OR DATEDIFF(mm,BugEvent.dt,GetDate())>6

Put the output of the above SQL into the parentheses of these and make sure to run in order:

DELETE FROM BugView WHERE ixBug in( SELECT_FROM_ABOVE )
DELETE FROM BugRelation WHERE ixBugTo in( SELECT_FROM_ABOVE )
DELETE FROM BugRelation WHERE ixBugFrom in( SELECT_FROM_ABOVE )
DELETE FROM Scout WHERE ixBug in( SELECT_FROM_ABOVE )
DELETE FROM TagAssociation WHERE ixBug in( SELECT_FROM_ABOVE )
DELETE FROM Duplicates WHERE ixBugDupe in( SELECT_FROM_ABOVE )
DELETE FROM Duplicates WHERE ixBugDupeOf in( SELECT_FROM_ABOVE )
DELETE FROM TitleWord WHERE ixBug in( SELECT_FROM_ABOVE )
DELETE FROM CVS WHERE ixBug in( SELECT_FROM_ABOVE )
DELETE FROM TimeInterval WHERE ixBug in( SELECT_FROM_ABOVE )
DELETE FROM Subscriptions WHERE ixBug in( SELECT_FROM_ABOVE )
DELETE FROM BugEvent WHERE ixBug in( SELECT_FROM_ABOVE )
DELETE FROM Bug WHERE ixBug in( SELECT_FROM_ABOVE )

Purge Old Cases for a Specific Project

Find the project id (or other identifier) and run these delete statements.

DELETE FROM Attachment
WHERE ixAttachment IN 
    (SELECT ixAttachment FROM AttachmentReference
     WHERE ixBugEvent IN 
        (SELECT BugEvent.ixBugEvent FROM BugEvent  
        LEFT JOIN Bug ON BugEvent.ixBug = Bug.ixBug  
        WHERE Bug.ixProject IN (123))
        );    
DELETE FROM AttachmentReference 
WHERE ixAttachmentReference IN
(SELECT AttachmentReference.ixAttachmentReference
        FROM AttachmentReference
        INNER JOIN BugEvent
            on BugEvent.ixBugEvent =AttachmentReference.ixBugEvent
        INNER JOIN Bug
            ON Bug.ixBugEventLatest =BugEvent.ixBugEvent
        WHERE Bug.ixProject IN (123))
DELETE FROM BugEvent  
  WHERE ixBug IN (SELECT ixBug FROM Bug WHERE ixProject=123);
DELETE FROM BugView  
  WHERE ixBug IN (SELECT ixBug FROM Bug WHERE ixProject=123);
DELETE FROM BugRelation  
  WHERE ixBugFrom IN (SELECT ixBug FROM Bug WHERE ixProject=123);
DELETE FROM BugRelation  
  WHERE ixBugTo IN (SELECT ixBug FROM Bug WHERE ixProject=123);
DELETE FROM Duplicates  
  WHERE ixBugDupe IN (SELECT ixBug FROM Bug WHERE ixProject=123);
DELETE FROM Duplicates  
  WHERE ixBugDupeOf IN (SELECT ixBug FROM Bug WHERE ixProject=123);
DELETE FROM Scout WHERE ixBug IN (SELECT ixBug FROM Bug WHERE ixProject=123);
DELETE FROM TagAssociation 
  WHERE ixBug >0 AND ixBug IN (SELECT ixBug FROM Bug WHERE ixProject=123);
DELETE FROM Bug WHERE ixProject =123;

Purge Old Case Attachments

Here’s how to delete old FogBugz attachments from the database that are not from emails. In our example, we’ll delete any attachments for bugs closed more than 6 months ago.

Just add a join on Bug and change the WHERE clause to use the case’s dtClosed instead of the date of the BugEvent

For FogBugz 7 and earlier:

MS SQL Server:

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

MySQL:

DELETE FROM Attachment WHERE ixAttachment IN
(SELECT Attachment.ixAttachment
        FROM Attachment
        INNER JOIN BugEvent
            ON Attachment.ixBugEvent =BugEvent.ixBugEvent
        INNER JOIN Bug
            ON Bug.ixBug =BugEvent.ixBug
        WHERE (DATEDIFF(Bug.dtClosed,CurDate())/30<=6) AND fEmail != 1
)

For FogBugz 8 and later:

MS SQL Server:

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

DELETE FROM AttachmentReference WHERE ixAttachmentReference IN
(SELECT AttachmentReference.ixAttachmentReference
        FROM AttachmentReference
        INNER JOIN BugEvent
            ON AttachmentReference.ixBugEvent =BugEvent.ixBugEvent
        INNER JOIN Bug
            ON Bug.ixBug =BugEvent.ixBug
        WHERE DATEDIFF(mm,Bug.dtClosed,GetDate())>6 AND fEmail != 1
)

MySQL:

DELETE FROM Attachment WHERE ixAttachment IN
(SELECT AttachmentReference.ixAttachment
        FROM AttachmentReference
        INNER JOIN BugEvent
            ON AttachmentReference.ixBugEvent =BugEvent.ixBugEvent
        INNER JOIN Bug
            ON Bug.ixBug =BugEvent.ixBug
        WHERE (DATEDIFF(Bug.dtClosed,CurDate())/30<=6) AND fEmail != 1
)

DELETE ar FROM AttachmentReference ar
 INNER JOIN BugEvent
         ON ar.ixBugEvent =BugEvent.ixBugEvent
 INNER JOIN Bug
         ON Bug.ixBug =BugEvent.ixBug
      WHERE (DATEDIFF(Bug.dtClosed,CurDate())/30<=6) AND fEmail != 1

Purge Old Email Attachments

Here’s how to delete old FogBugz email attachments from the database. In our example, we’ll delete any attachments for emails 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.

Try:

SELECT TOP 100 DATALENGTH(s) FROM BugEvent ORDER BY DATALENGTH(s) DESC

That will show you the top 100 biggest cases.

Then you could do something like:

UPDATE BugEvent SET s = '' HAVING DATALENGTH(s) > 10000000

(last number is in bytes)

Articles in this section