Recently I had to transfer all the blog posts from an old 2.4 version of WordPress, to a new 3.0 install. After I exported the content (via Tools > Export), and tried to import it into the new blog, the importer froze up and never completed. I was surprised to notice that the XML file was 50MB. Opening up the file in Notepad++ revealed that this was all spam. Apparently the blog’s spam had never been deleted and there were over 10,000 spam comments!
Attempting to delete these manually from the admin panels proved fruitless because it was only possible to delete a handful each time. It was evident that this would take too long, and that they needed to be deleted through the database.
Goodbye Unapproved Comments
If you have access to your database, through PHPMyAdmin or an external application, then you can run a simple query to delete all the spam comments:
DELETE from wp_comments WHERE comment_approved = '0';
In this case, I did not have access to the control panel information that would grant me access to the database. I couldn’t connect externally using the database info from WPCONFIG (many servers give you a different host name to connect with remotely). Instead of waiting days to hopefully get the right info from a busy client, I found a plugin that allows you run SQL queries called WP-DBManager. There are archived versions available going all the way back to WordPress 1.0.