0

Possible Duplicate:
Shrink my FogBugz database by removing old email attachments?

We have been using the full version of MS SQL Server Standard for our FogBugz 7.0 installation, but are now downgrading to MS SQL Server Express. However, SQL Server Express has a 4GB database limit and our database is over 6GB in size mostly due to many large file attachments from email.

How do I remove all file attachments over a certain size or older than a certain time period? I would like to maintain the text from the cases themselves for reference, but do not need the attachments.

Thanks

Terrell

flag

closed as exact duplicate by adambox♦♦ Sep 12 2011 at 16:24

2 Answers

1

There is an Attachments table, but if you think most of your big attachments came in via email, they won't be in there.

They will be in the BugEvent.s column.

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)

link|flag
Thanks Michael, that's what I was looking for. I wasn't clear in my original post that it was email attachments. Will the update query wipe out the text of the email message too or just the file? I guess if it wipes out the file too then I could set s = 'Message removed due to large attached file'? – Terrell Feb 4 2010 at 2:10
@Terrell It will wipe the message as well as the attachments. – Michael Pryor♦ Feb 4 2010 at 19:45
I found out some large large files were in BugEvent and others were in Attachment. Below are the two queries I ran to cleanup my database. UPDATE BugEvent SET s = '' WHERE DATALENGTH(s) > 2000000 and DELETE FROM Attachment WHERE DATALENGTH(sData) > 2000000 – Terrell Feb 5 2010 at 18:34
0

I think you should get your answers from the FB Database Schema.

Note that you can also get the schema of your database by querying the /default.asp?pg=pgSchema URL.

link|flag

Not the answer you're looking for? Browse other questions tagged or ask your own question.