Our FB SQL Server database is getting very large (over 50 GB). The space itself is not necessarily a concern, but it seems to be starting to affect performance.
We have less than 70,000 cases. Mathematically, that averages to over 700KB per case. We suspect that most of this space is taken by emails stored in dbo.BugEvent.s (ntext). Most email clients will include the original message with every email reply. So as people continue to reply to a case, the same data is getting duplicated over and over as "Original Message".
Has anyone ever considered running a SQL script that will go through each BugEvent and truncate everything after "-----Original Message----", so it is storing only the new email content? I realize that this is an over generalization, because there are times when someone forwards an email to FB and a new case is created (so the "Original Message" is not already stored in the case). I am just wondering, at a high level, if something like this would have any significant improvements on space or performance.
How about using SQL Server FILESTREAM? Has anyone (aka FogCreek) considered using the FILESTREAM functionality in SQL Server to store the blobs? This would not reduce the overall space used, it would just redistribute it (moving the blobs outside the database). But decreasing database size by moving the blobs could potentially improve performance.
