CS Tidbits #25: Disable Stale Spam Comment Auditing

I've been in the process of building some Community Server add-ins lately (namely a couple of Control Panel add-ins) and stumbled across today's tidbit: Comments that are deemed as spam (they have accrued enough points to be considered as such) and are also flagged as stale (they've been sitting in your database for x number of days) are cleaned up on a regular basis by the DeleteStaleSpamComments job which runs every x number of days that you configure it to fire. 

There are a couple of issues with this process:  A) the job interval will get reset if the asp.net process hosting your CS installation restarts before the configured number of days pass by (which I will address in a future Tidbit) and thus will never fire, and B) while the comments are indeed deleted from the cs_posts table, they are archived into the cs_posts_deleted_archive table, so whatever space they were taking up isn't actually freed back to your database.  The fix for this is easy enough...locate the cs_weblog_DeleteStaleSpamComments stored procedure and delete the following block of SQL:

INSERT cs_posts_deleted_archive (PostID, UserID, SectionID, SettingsID, ApplicationType, Body, Subject, IPAddress, DeletedDate, ParentID, ThreadID, PostLevel, PostType, PropertyNames, PropertyValues, SpamScore, PostStatus) SELECT P.PostID, P.UserID, P.SectionID, P.SettingsID, 1, P.Body, P.Subject, P.IPAddress, getdate(), P.ParentID, P.ThreadID, P.PostLevel, P.PostType, P.PropertyNames, P.PropertyValues, P.SpamScore, P.PostStatus FROM cs_Posts P INNER JOIN @CommentsToDelete D ON P.PostID = D.PostID

That is unless you really want all of your comment spam archived in case you decide one day you want them back.

Filed under:


# Community Server Bits said:

Jayson Knight with CS Tidbit #25: Disabling Stale Spam Comment Auditing. As Jayson explains, when comments

Monday, March 12, 2007 5:59 AM