Jeff,

Are you running Microsoft SQL Server or MySQL?

In either case, SpamFilter stores your quarantined spam emails in the database, but should remove old emails automatically. The number of days for which emails are stored is user-defined using a setting in the "Settings - Database Setup" tab in SpamFilter. Reducing this number will reduce the amount of DB space on disk, even though you will possibly have to compact the database (how it's done is platform-dependent) to regain the space.

With Microsoft SQL Server there is the additional hassle of having to maintain the SQL Server transaction logs (depending on how your SpamFilter database was configured), as those can grow quite large.

Jeff,

Hundreds of thousands or several millions can be normal if you receive a lot of emails. The best way to see if the automatic deletion is working is to look at the first dozen of records in the tblQuarantine table. The records will have a field called "MsgDate" that contains the date stamp for the emails that were received. None of the emails in the database should have values in the MsgDate fields that are older than 30 days. If you do have many (as in thousands) records which have older dates, then there is indeed an issue with SpamFilter not being able to delete older messages.

If SpamFilter does have issues deleting the records, you can manually delete them by executing the following query in SQL Server on the SpamFilter database:


SET ROWCOUNT 500
delete_more1:
UPDATE tblQuarantine SET Expire = 1 WHERE (DATEDIFF(day, MsgDate, GETDATE()) > 30) AND Expire = 0
IF @@ROWCOUNT > 0 GOTO delete_more1
SET ROWCOUNT 0

SET ROWCOUNT 500
delete_more2:
DELETE FROM tblQuarantine WHERE tblQuarantine.Expire <> 0
IF @@ROWCOUNT > 0 GOTO delete_more2
SET ROWCOUNT 0

SET ROWCOUNT 500
delete_more3:
DELETE tblMsgs FROM tblMsgs LEFT JOIN tblQuarantine
ON tblMsgs.MsgID = tblQuarantine.MsgID WHERE (tblQuarantine.MsgID IS NULL)
IF @@ROWCOUNT > 0 GOTO delete_more3
SET ROWCOUNT 0


The only parameter you may want to change is that big “30” that specifies the number of days to hold the emails. This procedure uses loops to update/delete 500 rows of data at a time, and this avoid extensive table/row locking to increase performance and reduce database timeouts.

Records can also be deleted from the database with a simple "DELETE" query to remove all old entries, but doing so can place locks on the database preventing SpamFilter from adding new records while the delete process occurs in the background.



Edited by LogSat - 27 December 2010 at 11:10pm

The growth of the DB depends on the amount of emails you receive. If for example you receive for example 100,000 emails/day, on average that will result in the database growing 1GB per day.

An increase of 800MB in half an hour would indicate a flow of about 4 million emails/day, which I do not believe is your case (please do correct me if the assumption is wrong). However...

..In your 1st post you say that the database is over 9GB, but without going into specifics on the size of the .MDF and of the .LDF files. Only now we see that they are 9.2GB and 1.2GB. As I mentioned before, maintaing the SQL transaction logs (the .LDF files) on a SQL Server is a hassle that database administrators need to beware of, as these transaction logs can become very large. If your .ldf files is 1.2GB *after* you compacted the database, that usually indicates that you (or the DBA) did not truncate the transaction log. If you perform routine backups, and are willing to sacrifice the ability to restore the database at any specific point in time in case of disasters, and are comfortable with just having the ability to restore the database in its entirety only (without the point-in-time option), then I'd recommend you configure the database(s) to use the "Simple" recovery model in SQL Server. This almost completely eliminates the issue with large growing .LDF files and will allow you to have more room.

You can try saving