Spam Filter ISP Forums : quarantine release process

quarantine release process : I hope not...but I don't...

Tue, 16 May 2006 11:14:29 +0000

Author: Terry
Subject: 5610
Posted: 16 May 2006 at 11:14am

I hope not...but I don't know how to prove it

quarantine release process : Terry, So ... you think that...

Tue, 16 May 2006 10:57:55 +0000

Author: Desperado
Subject: 5610
Posted: 16 May 2006 at 10:57am

Terry,

So ... you think that you did not actually loose anything EXCEPT orphaned messages?

quarantine release process : In the tblmsgs table we had approximately...

Tue, 16 May 2006 10:54:39 +0000

Author: Terry
Subject: 5610
Posted: 16 May 2006 at 10:54am

In the tblmsgs table we had approximately 84,027 messages before the statement ran...and purged 59,399 of them....in the tblQuarantine table we had closer to 25,000 records.

quarantine release process : Terry, I too am working on this...

Tue, 16 May 2006 10:49:27 +0000

Author: Desperado
Subject: 5610
Posted: 16 May 2006 at 10:49am

Terry,

I too am working on this with LogSat. How many records did you have before and after the query?

quarantine release process : Well that did seem to help...statement...

Tue, 16 May 2006 09:31:55 +0000

Author: Terry
Subject: 5610
Posted: 16 May 2006 at 9:31am

Well that did seem to help...statement #1 took about 2 seconds and affected 6 rows, statement #2 took about 1 second and affected 38 rows, statement #3 took 12 minutes and 36 seconds and affected 59,399 rows (lots of orphaned messages).

After running the statements from sql analyzer I am able to run the new version spamfilter with no timeouts so far. It is running at this time and I will monitor it during the day today.

quarantine release process : The more I look into the possible...

Mon, 15 May 2006 23:08:30 +0000

Author: LogSat
Subject: 5610
Posted: 15 May 2006 at 11:08pm

The more I look into the possible cause in this, the more everything points to a timeout issue with the SQL server.
This new buld of SpamFilter has new SQL queries to clear out the quarantine database. One of the queries is also designed to perform better housekeeping, by deleting any orphan records that may be present in the DB. If your database is large, the initial "cleanup" query may require a lot of resources from the SQL server. Depending on the SQL server's hardware (CPU and RAM) this may cause other queries to timeout while the cleanup queries are executing.

Could you try to manually execute the cleanup procedures? Please try executing the following in SQL Analyzer:

UPDATE    tblQuarantine
SET         &nb sp;    Expire = 1
WHERE     (MsgDate < DATEADD(day, - 14, GETDATE()))
GO

DELETE FROM tblQuarantine WHERE tblQuarantine.Expire <> 0
GO

DELETE tblMsgs FROM tblQuarantine RIGHT OUTER JOIN tblMsgs
    ON tblQuarantine.MsgID = tblMsgs.MsgID WHERE (tblQuarantine.MsgID IS NULL)
GO

You can also try to execute the 3 queries, in the order given, one at a time in the SQL Enterprise Manager. Please monitor the CPU and RAM on the SQL server while they execute to see if they spike (and remain spiked) for a while (minutes).

This cleanup should take the most resources only the 1st time ever it's run, and that is only if there were orphan records to begin with in the database.

After this is done, could you please, once more, try running the latest SpamFilter?

Thanks.

quarantine release process : Same problem Roberto....moved...

Mon, 15 May 2006 20:03:10 +0000

Author: Terry
Subject: 5610
Posted: 15 May 2006 at 8:03pm

Same problem Roberto....moved the file into the directory after stopping the service...ran it for a couple of minutes...here is another extract...if you want the whole day...it would be better for me to do it tomorrow as that would be a fresh log

5/15/06 16:55:37:481 -- (5776) - MAPS search done...
05/15/06 16:55:37:481 -- (5776) RCPT TO: Stan.Jones@portofportland.com accepted
05/15/06 16:55:37:528 -- (5100) Connection from: 220.150.169.59 - Originating country : Japan
05/15/06 16:55:37:653 -- (5776) EMail from Stan@terrahydrinc.com to Stan.Jones@portofportland.com passes Bayesian filter - 0% spam (0ms)
05/15/06 16:55:37:715 -- (5160) Error occurred during RECEIVEMESSAGE when accessing the quarantine database: Timeout expired ( 1 2 2b 3 3a 4 5 6 7 8 9 11 12 13 15 16 17 18 18b 19 29 30 31 32 33 54 55 56 77 78 79 80 81 82 83 84 85 86 87 88)
05/15/06 16:55:37:715 -- (5160) EMail from cpuhoqaqe@dow.jones.com to brantm@portptld.com, bailej@portptld.com, biebeg@portptld.com was received and quarantined. Size: 11 KB, 11264 bytes
05/15/06 16:55:37:746 -- (5424) Time to add Msg to Bayes corpus:0
05/15/06 16:55:37:825 -- (5776) EMail from Stan@terrahydrinc.com to Stan.Jones@portofportland.com was queued. Size: 1 KB, 1024 bytes
05/15/06 16:55:37:840 -- (5484) Sending email from Stan@terrahydrinc.com to Stan.Jones@portofportland.com
05/15/06 16:55:37:856 -- (5424) Time to add Msg to Bayes corpus:15
05/15/06 16:55:37:965 -- (5776) Disconnect
05/15/06 16:55:37:981 -- (5484) EMail from Stan@terrahydrinc.com to Stan.Jones@portofportland.com was forwarded to portexfe.pop.portptld.com:25
05/15/06 16:55:38:262 -- (5160) Blacklist cache - Added 218.155.140.30 to limbo
05/15/06 16:55:38:387 -- (5100) Resolving 220.150.169.59 - 59.169.150.220.ap.yournet.ne.jp
05/15/06 16:55:38:715 -- (5924) Connection from: 208.187.190.34 - Originating country : United States
05/15/06 16:55:39:559 -- (5924) Resolving 208.187.190.34 - cp.ipnshosting.com
05/15/06 16:55:39:559 -- (5924) - Empty Mail From -
05/15/06 16:55:39:559 -- (5924) 208.187.190.34 - Mail from: To: Eric.Hedaa@portofportland.com will be rejected
05/15/06 16:55:39:934 -- (5504) Error occurred during RECEIVEMESSAGE when accessing the quarantine database: Timeout expired ( 1 2 2b 3 3a 4 5 6 7 8 9 11 12 13 15 16 17 18 18b 19 29 30 31 32 33 54 55 56 77 78 79 80 81 82 83 84 85 86 87 88)
05/15/06 16:55:39:934 -- (5504) EMail from valerie89k@earthlink.net to oestem@portptld.com was received and quarantined. Size: 1 KB, 1024 bytes
05/15/06 16:55:39:965 -- (5424) Time to add Msg to Bayes corpus:15
05/15/06 16:55:40:403 -- (5504) Blacklist cache - Added 217.165.190.89 to limbo
05/15/06 16:55:40:403 -- (5504) Disconnect

quarantine release process : You're correct on the "wasn't...

Mon, 15 May 2006 18:23:00 +0000

Author: LogSat
Subject: 5610
Posted: 15 May 2006 at 6:23pm

You're correct on the "wasn't sure any new stuff was getting there". With that error, emails where not being quarantined.

I'd like to ask you to try again if you can, as there is nothing in this build that would explain this behavior, other than a temporary issue with the SQL server.

If you wish, you could rename the SpamFilter.exe file in the zip update to something else, like SF.exe, and copy it in the SpamFilter directory.
You can then stop SpamFilter, and then run the new SF.exe for a few seconds to see if the error occurs. SF.exe will run in standalone, non-service mode, and you will see from the GUI what is happening.

If the problem still persists, please zip and email us SpamFilter's activity log for the day so we can try to see what is going on.

quarantine release process : the select statement worked just...

Mon, 15 May 2006 16:24:18 +0000

Author: Terry
Subject: 5610
Posted: 15 May 2006 at 4:24pm

the select statement worked just fine and returned 0 rows. I had to uninstall the fix because it appeared to be failing in access to the quarantine and I wasn't sure any new stuff was getting there...

quarantine release process : Terry,Are you still having the...

Mon, 15 May 2006 16:17:02 +0000

Author: LogSat
Subject: 5610
Posted: 15 May 2006 at 4:17pm

Terry,

Are you still having the problem? From the logs, even though it may appear a strange coincidence, the problem is completely separate from the one that was patched with the last build.

The error is very specific (a timeout on a query), and all those weird numbers pinpoint the query that caused the error. It's:

SELECT * FROM tblQuarantine WHERE 0=1;

can you please try executing that query on the SpamFilter database from either SQL's Enterprise Manager or the Query Analyzer tool? It should retuen an empty result set with no records displayed. If it retuens an error, that would indicate issues with either the database/database server.