Overview

At times, you may need to delete old case attachments or email attachments from the database to save space.

Deleting email or non-email attachments involves the same process and database queries, just the used attachment type flag (fEmail) is different.

This article applies only to On-Premise versions of FogBugz.

For On-Demand accounts, if you need help in deleting numerous cases, the Professional Services team could help. But first, please reach out to Succes Team for a quote.

Solution

Identify Attachments To Be Removed

When your FogBugz database size increases due to a high number of cases, and shrinking does not help, you might want to consider removing older and bigger attachments from your database.

Run the following query on your FogBugz database to identify potential attachments for removal:

SELECT 
	bug.ixBug, bug.sTitle, bug.dtClosed,
	bugEvent.fEmail, 
	att.ixAttachment, (DATALENGTH(att.sData) / 1024 ) as sData_AttachmentSize_In_KBytes
		
	FROM dbo.Attachment att
	LEFT JOIN dbo.AttachmentReference ar
	  ON ar.ixAttachment = att.ixAttachment
	LEFT JOIN dbo.BugEvent bugEvent
	  ON bugEvent.ixBugEvent = ar.ixBugEvent
	LEFT JOIN dbo.Bug bug
	  ON bug.ixBug = bugEvent.ixBug

	WHERE bug.dtClosed IS NOT NULL -- remove this line if you want to list also the open cases
	ORDER BY bug.dtClosed DESC, DATALENGTH(att.sData) DESC

Explanation of the query results:

dtClosed: shows the date when the case was closed. Use this column to filter based on the closure date. If NULL, case is still open (you don't want to remove open cases, but you might be interested if they have huge attachments).
fEmail: if 1, than it is an email attachment, otherwise is a non-email case attachment
sData: the size of the attachment in bytes. The query above converts it to KBytes. If attachment is old and big, you might consider removing it.
ixBug & ixAttachment: if ixAttachment is NOT NULL in the query above, but ixBug is NULL, it means the given attachment is "rogue", it is not linked anymore to existing cases (case might have been deleted from database). You may delete such attachments by filtering based on their ixAttachment (the attachment ID in the Attachment table).

Back to the top

Remove the Attachments

Warning: Always make a backup of your database before updating it. If possible, run the corresponding queries in your test environment first. The queries listed below make irreversible changes to your database.

Note: The example queries listed below will remove attachments for all cases that:

were closed more than 6 months ago.
contain non-email case attachments (fEmail != 1).

Please adjust the queries according to your needs:

change the date range applied to Bug.dtClosed
change the attachment type (fEmail = 1 for email, or remove the fEmail clause to delete all types of attachments)
add a filter for attachment size: DATALENGTH(Attachment.sData) > (1024 * 1024 * N MBytes)
to delete attachments that are not linked to any case event (rogue attachments), then:
- change all the INNER JOIN to LEFT JOIN, and
- filter using the WHERE Bug.ixBug IS NULL

FogBugz 8 and Higher Versions

On your FogBugz Microsoft SQL Server database, please run the following query:

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
	)

FogBugz 7 and Earlier Versions

On your FogBugz Microsoft SQL Server database, please run the following query:

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

Back to the top

Reindexing after data removal

If a lot of attachments were removed, we recommend rebuilding the database table indexes for Attachment and AttachmentReference tables.

Back to the top

Testing

These queries do not delete the case history, just remove the attachments.

After the attachment removal process, you will see in the case view the placeholder links for the (removed) attachments, but FogBugz will not be able to open the given attachments.

Back to the top