Overview

At times, you may need to archive your old FogBugz data to save space. This article describes how to delete old cases from the database, including the instructions to delete cases only for a specific project.
If you have any questions, please do not hesitate to contact us.

Note: This process is applicable for FogBugz For Your Server, On-Site, and On-Premises. If you need to delete numerous cases from your On-Demand account, please contact us.

Process

Warning: Always make a backup of your database before directly working with it. If possible, run the corresponding queries in your test environment first.

Find the required cases. Review them to make sure that you want to delete all of them.

Note: In this example, we will delete all cases that are older than 6 months. Hence, make sure to adjust the query according to your needs (i.e., replace 6 with the required number of months).
```
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 old case attachments.

For FogBugz 8 and higher versions, run the following query to delete the case attachments:

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)

Also, run the following query to delete the attachment references:

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)

If you are using FogBugz 7, run the following query to delete the attachments:

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 the required cases.

Run the following query and copy its output.

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

Paste the output of the above query into the parentheses below and run the following statements:

DELETE FROM BugView WHERE ixBug in( <output of the SELECT query> );
DELETE FROM BugRelation WHERE ixBugTo in( <output of the SELECT query> );
DELETE FROM BugRelation WHERE ixBugFrom in( <output of the SELECT query> );
DELETE FROM Scout WHERE ixBug in( <output of the SELECT query> );
DELETE FROM TagAssociation WHERE ixBug in( <output of the SELECT query> );
DELETE FROM Duplicates WHERE ixBugDupe in( <output of the SELECT query> );
DELETE FROM Duplicates WHERE ixBugDupeOf in( <output of the SELECT query> );
DELETE FROM TitleWord WHERE ixBug in( <output of the SELECT query> );
DELETE FROM CVS WHERE ixBug in( <output of the SELECT query> );
DELETE FROM TimeInterval WHERE ixBug in( <output of the SELECT query> );
DELETE FROM Subscriptions WHERE ixBug in( <output of the SELECT query> );
DELETE FROM BugEvent WHERE ixBug in( <output of the SELECT query> );
DELETE FROM Bug WHERE ixBug in( <output of the SELECT query> );

If you need to remove old cases only for a specific project, please run the following statement (please specify the required Project id):

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 (<Project id>))
        ); 
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 (<Project id>))
DELETE FROM BugEvent 
  WHERE ixBug IN (SELECT ixBug FROM Bug WHERE ixProject=<Project id>);
DELETE FROM BugView 
  WHERE ixBug IN (SELECT ixBug FROM Bug WHERE ixProject=<Project id>);
DELETE FROM BugRelation 
  WHERE ixBugFrom IN (SELECT ixBug FROM Bug WHERE ixProject=<Project id>);
DELETE FROM BugRelation 
  WHERE ixBugTo IN (SELECT ixBug FROM Bug WHERE ixProject=<Project id>);
DELETE FROM Duplicates 
  WHERE ixBugDupe IN (SELECT ixBug FROM Bug WHERE ixProject=<Project id>);
DELETE FROM Duplicates 
  WHERE ixBugDupeOf IN (SELECT ixBug FROM Bug WHERE ixProject=<Project id>);
DELETE FROM Scout WHERE ixBug IN (SELECT ixBug FROM Bug WHERE ixProject=<Project id>);
DELETE FROM TagAssociation 
  WHERE ixBug >0 AND ixBug IN (SELECT ixBug FROM Bug WHERE ixProject=<Project id>);
DELETE FROM Bug WHERE ixProject = <Project id>;

Recommendations

After deleting cases directly from the FogBugz database, it is highly recommended to perform a backfill index reset to make sure that the Elastic Search indexes are updated.

Since this process will remove data from the database, it is also highly recommended to rebuild the database table indexes to reduce the disk space and repair any broken indexes.

Purging Old FogBugz Data

Overview

Process

Recommendations

Related Articles