Print Page | Close Window

Spamfilter 3.1.3.615

Printed From: LogSat Software
Category: Spam Filter ISP
Forum Name: Spam Filter ISP Support
Forum Description: General support for Spam Filter ISP
URL: https://www.logsat.com/spamfilter/forums/forum_posts.asp?TID=6544
Printed Date: 15 March 2025 at 7:55am


Topic: Spamfilter 3.1.3.615
Posted By: jwutzke
Subject: Spamfilter 3.1.3.615
Date Posted: 25 August 2008 at 2:27pm
I have spamfilter running on win2k with SQL installed, has been running for quite awhile(long before I got here)
 
Well here is my problem it is on a 14 gig hard drive and it is filling up (had to remove the
i386 dir for space today), the database is over 9 gigs and then webshield is a couple, What
I need to know is what is stored in the SQL database and how to remove say anything before 6 months ago. Is this something that can be done? Or should I look at building a new VM to run it with more space and move it to there?
 
--Jeff Wutzke
 


Replies:
Posted By: LogSat
Date Posted: 25 August 2008 at 4:26pm
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.


-------------
Roberto Franceschetti

http://www.logsat.com" rel="nofollow - LogSat Software

http://www.logsat.com/sfi-spam-filter.asp" rel="nofollow - Spam Filter ISP

Posted By: jwutzke
Date Posted: 25 August 2008 at 5:20pm
Oh sorry, I am running MS SQL.  so on the Quarantine tab I have it set to 30 days. but in say the tblMsgs there are hundreds of thousands of lines.  so it looks to me like the old data is not being deleted.
 
is there a way to force delete from the Quarantine database?
 
--Jeff
 

Posted By: LogSat
Date Posted: 25 August 2008 at 8:18pm
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.



-------------
Roberto Franceschetti

http://www.logsat.com" rel="nofollow - LogSat Software

http://www.logsat.com/sfi-spam-filter.asp" rel="nofollow - Spam Filter ISP

Posted By: jwutzke
Date Posted: 26 August 2008 at 2:37pm
And so the saga continues
 
 
I deleted and rearanged to get 2.1 gigs of free space on the partition that spamfilter database is stored. within 30 min the following database files grew to the following numbers spamfilter.mdf 9,210,368 KB and spamfilter_log.LDF 1,206,106 KB leaving me 1.25 gigs on the hard drive. So they ate up 800 megs in 30 min. I have changed the retention of the quarantined database from 30 to 15 days and have verified that is all that is being saved. My question is what is taking up all the space in the database, and what is growing so quickly?
 
 

Posted By: LogSat
Date Posted: 26 August 2008 at 4:54pm
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

-------------
Roberto Franceschetti

http://www.logsat.com" rel="nofollow - LogSat Software

http://www.logsat.com/sfi-spam-filter.asp" rel="nofollow - Spam Filter ISP


Print Page | Close Window