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
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)