I am getting a lot of database is busy - cannot quarantine......
In fact, I am seeing very few messages that are successfully quarantined.  I am able to read and write to the DB, such as to add a new domain.
Any ideas?  I rolled back to the 676, and still have the same issue.  Running MSSQL 2000 SP4, and .677 in Enterprise Mode.

Thanks

Roberto -
Right again you are.  Earlier today, in an effort to control the size of the growing database, we turned on the option to "not quarantine" on the country filter.  I then did an update query to expire all emails with a reason code of 8.  That turned out to be about 550K emails.  And thus you know where that got me

So, right now, SFE is off, and the database is running your script over and over to cleanup the database.  Hopefully, by morning, I'll be able to start everything up again.

Problems are one thing, but it sure bites your butt when the problems were caused by no one but yourself

Thanks again for all of the great help!

G'Night

After several hours, the database was cleaned and everything was started back up without any other issues.  I am running the script that Roberto posted above every 10 minutes and it appears to be working perfectly.

Thanks again for all the help.

jerbo128

Hello Simone,

We run a couple of scripts for doing this.
We run these scripts on linux under supervision of daemontools.

Script 1. sf.expire.old.records.pl
#!/usr/bin/perl
# Find all records older than 11 days and set their exipre to 1
BEGIN{
   use FindBin; unshift @INC, ($FindBin::Bin , "$FindBin::Bin/../lib");
}
$|++;


use DBI;

$dbserver="servername";
$dbname="dbname";
$dbuser="username";
$dbpass="password";


my $dsn = "DBI:mysql:database=$dbname;host=$dbserver";
$dbh=DBI->connect_cached($dsn, $dbuser, $dbpass);

use vars qw($dbh);


$query="update tblQuarantine set Expire=1  where  Expire=0 and date_add(MsgDate, interval 11 day)<now() limit 100";
$sth=$dbh->prepare($query);


while(true){
        $sth->execute();
        if ($sth->rows < 10) {
                 print "Less than 10 rows affected. Disconnecting from the database, sleeping for one hour and then exiting\n";
                 $dbh->disconnect or warn $dbh->errstr;
                 sleep 3600;
                 exit;
        }
        print "Rows affected: ", $sth->rows, "\n";
        sleep(1);
}
__END__

Script 2. sf.purge.expired.records.pl

#!/usr/bin/perl
# Find all expired rows and delete from both tblquarantine and tblmsgs
BEGIN{
   use FindBin; unshift @INC, ($FindBin::Bin , "$FindBin::Bin/../lib");
}
$|++;

use DBI;

$dbserver="servername";
$dbname="dbname";
$dbuser="username";
$dbpass="password";


my $dsn = "DBI:mysql:database=$dbname;host=$dbserver";
$dbh=DBI->connect_cached($dsn, $dbuser, $dbpass);

use vars qw($dbh);


# be nice to the db. dont select too much at a time
$query="select QuarID, MsgID from tblQuarantine where Expire=1 limit 10";
$sth=$dbh->prepare($query);

$quar_del_sth=$dbh->prepare("delete from tblQuarantine where QuarID=? limit 1");
$msg_del_sth=$dbh->prepare("delete from tblMsgs where MsgID=? limit 1");


while(true){
    $sth->execute();


    while (($QuarID, $MsgID)=$sth->fetchrow_array()){
       print "Purging QuarID $QuarID, MSGID $MsgID\n";
       $quar_del_sth->execute($QuarID);
       $msg_del_sth->execute($MsgID);
    }
}
__END__

Would it help if I revoke delete right of the user that is doing the quarantine?

Roberto,

Thanks. I will wait for the next build rather than hacking in something.

Another possibility I was looking at was to implement the recently released Alpha MysqlProxy. This will allow me to intercept and rewrite/drop/give_fake_result for the delete from tblqurantine left join.... command.

Atif,

We're releasing a new build right now, please see the release notes below.

// New to VersionNumber = '3.5.4.702';
{TODO -cNew : Due to memory leak issues and Access Violations in the PDF library used, by default SpamFilter will not scan for images within PDF files by setting the value SpamPDFMaxPagesToScan=0 in the SpamFilter.ini file. If this setting was enabled in a previous version however, it will remain enabled after upgrading to this version}
{TODO -cFix : Due to memory leak issues and Access Violations in the PDF library used, we have been forced to remove the ability to scan keywords within PDF files. We may re-add this functionality at a later time when a more stable PDF library is available}
{TODO -cNew : Added new option ForceDisconnectOnNonAuthorizedTO in SpamFilter.ini file to change the default behavior that forces a disconnect when a recipient is specified that is not in the AuthorizedTO list}
{TODO -cNew : Added new option DoNotDeleteExpiredEmailsFromQuarantine in SpamFilter.ini to prevent SpamFilter to perform the routine cleanup of the quarantine database by deleting old archived emails. Useful if admins want to perform their own cleanup}

This change only affects users who implemented the "AuthorizedTO" whitelist that specifies the list of valid emails accounts to receive email for.

In the past, SpamFilter has always forced a disconnect of the remote server if they specified an incorrect address in the RCPT TO command. This prevented the spammers from harvesting valid email addresses, and also greatly reduced spam as if the spammer would try again to send emails to different invalid addresses, after a few retries they would eventually be blocked by the IP cache filter, which cached the bad IP and blocked further connections for a few minutes.

The downside is that, if there was an email with multiple recipients, and one of them was misspelled or simply did not exist anymore, this would have caused the sender to be disconnected, and cause delivery problems to valid addresses as well.

If the Bayesian filter is enabled, and the "Receive full emails if recipient not in AuthorizedTO whitelist" option was checked, SpamFilter's behavior is different. In this scenario, if a recipient is not in the "AuthorizedTO" whitelist, SpamFilter will reject the invalid recipient, but will continue to accept further RCPT TO commands for more recipients.

With this build, we added another option in the SpamFilter.ini file that will achieve this same functionality, even if the Bayesian filter is disabled.