Delete/purge old attachments from the FogBugz database - FogBugz Knowledge Exchange most recent 30 from http://fogbugz.stackexchange.com2013-05-21T06:02:05Zhttp://fogbugz.stackexchange.com/feeds/question/5901http://www.creativecommons.org/licenses/by-nc/2.5/rdfhttp://fogbugz.stackexchange.com/questions/5901/delete-purge-old-attachments-from-the-fogbugz-databaseDelete/purge old attachments from the FogBugz databaseMichael Pliskin2010-11-15T12:46:24Z2012-03-27T19:24:03Z
<p>I want to purge attachments for the bugs closed more than 6 months ago. How do I do that?</p>
<p>See Also:</p>
<ul>
<li><a href="http://fogbugz.stackexchange.com/questions/1038/shrink-my-fogbugz-database-by-removing-old-attachments" rel="nofollow">Deleting cases</a></li>
<li><a href="http://fogbugz.stackexchange.com/questions/10190" rel="nofollow">Deleting old cases</a></li>
<li><a href="http://fogbugz.stackexchange.com/questions/1038" rel="nofollow">Deleting old email attachments</a></li>
</ul>
http://fogbugz.stackexchange.com/questions/5901/delete-purge-old-attachments-from-the-fogbugz-database/6052#6052Answer by adambox for Delete/purge old attachments from the FogBugz databaseadambox2010-11-23T21:43:05Z2012-03-27T19:24:03Z<p>Just add a join on Bug and change the WHERE clause to use the case's dtClosed instead of the date of the BugEvent</p>
<h2>For FogBugz 7 and earlier:</h2>
<p><strong>MS SQL Server:</strong></p>
<pre><code>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
)
</code></pre>
<p><strong>MySQL:</strong></p>
<pre><code>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
)
</code></pre>
<h2>For FogBugz 8 and later:</h2>
<p><strong>MS SQL Server:</strong></p>
<pre><code>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
)
</code></pre>
<p><strong>MySQL:</strong></p>
<pre><code>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
</code></pre>