Purging Old FogBugz Data


Follow

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 reach out to our Success Team at success@fogbugz.com.


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.
  1. 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
    
  2. 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)
  3. 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.



Related Articles

 

Back to top