★ Black Friday★ Cyber MondayWeb hosting up to 85% off, checked on each host’s own siteGrab the deals →

Reduce WordPress Database Size: Delete Akismet Spam Before Backup

Share

Does your connection time out while you back up a huge WordPress database? The useful part of your database is often much smaller than it looks. This guide shows how to reduce WordPress database size before a backup by deleting spam comments caught by Akismet and clearing other junk, based on what worked on this blog.

Reduce WordPress database size guide card

Quick answer: Stored spam comments can make up a large share of a WordPress database. Go to Comments, open the Spam tab, check for any real comments, then click Empty Spam. Also clear old pending comments, trashed items and extra post revisions. Your next backup will be smaller, faster to download and less likely to fail.

How spam comments bloated my database backup

When I backed up my WordPress database with phpMyAdmin before a WordPress upgrade, the file was over 41MB. It took ages to download and I couldn’t get an error-free copy. Then I realised the tables that really mattered were not that big.

I use the Akismet plugin, which had blocked over 400,000 spam comments on this blog by then. That day, Akismet was storing over 20,000 comments marked as spam (about a week’s worth of spam for us). I deleted all of them, backed up again, and the new .sql file was just 7MB. Zipped, it shrank to 2MB.

My weekly automatic backups also varied from 3MB to 7MB, and the difference came down to how much Akismet spam had piled up when each backup ran.

How to reduce WordPress database size before a backup

  1. Empty the spam folder. In your dashboard go to Comments and click the Spam tab. Skim for any genuine comments and mark them Not Spam, then click Empty Spam to delete the rest permanently.
  2. Clear the moderation queue. Approve real pending comments and delete the junk. Thousands of pending comments also take up space.
  3. Empty the trash. Trashed comments, posts and pages stay in the database until they are emptied. See how to empty WordPress trash automatically.
  4. Limit post revisions. Every save can create a revision. Our guide explains how to delete post revisions in WordPress and limit them.
  5. Optimize the tables. After deleting data, optimizing tables reclaims unused space. Follow our steps to optimize the WordPress database safely.
  6. Compress the backup. A zipped or gzipped .sql file is usually a fraction of the original size.

Let Akismet clean up spam to reduce WordPress database size

Akismet (external site, opens in new tab) now deletes old spam automatically. By default it keeps spam comments for about 15 days before removing them, so the spam folder no longer grows forever the way it did in 2007. If you get a lot of spam, emptying the folder by hand before a backup still helps. Fewer spam comments arriving in the first place helps too; see these WordPress plugins to stop comment spam.

Back up your WordPress database the easy way

Large database files cause timeouts and broken downloads in phpMyAdmin, so a smaller database makes every method more reliable. Today you can back up with a plugin such as UpdraftPlus (external site, opens in new tab), which can send backups to cloud storage on a schedule, or use your web host’s backup tools. Our full guide covers how to back up and restore a WordPress database step by step.

Whichever method you use, the steps above to reduce WordPress database size will make each backup quicker. Always keep at least one recent backup before you delete anything in bulk, and test that you can restore it.

FAQ

Do spam comments slow down WordPress?

A huge number of stored spam comments makes the comments tables bigger, which can slow down backups and some admin screens. Emptying spam regularly keeps things lean.

Is it safe to empty the spam folder?

Yes, but it is permanent. Quickly check for real comments first and mark them Not Spam before you click Empty Spam.

How long does Akismet keep spam?

By default Akismet keeps spam comments for about 15 days, then deletes them automatically.

What else makes a WordPress database large?

Post revisions, trashed items, old plugin tables and expired temporary data (transients) are common causes. Clean them up, then optimize the tables.

Found this useful? Share it with someone who needs it.

Subscribe to our weekly tech newsletter

New tech guides, free tools and 2-minute fixes, once a week.

🎁 100 Tech Terms ebook comes free with every subscription.

Unsubscribe anytime. See our privacy policy.

Related Tech Guides

Leave a Reply

Your email address will not be published. Required fields are marked *

10 Comments

  1. 400,000 of spam comments? :O
    are you serious mate?
    y didn’t you delete them regularly?
    by the way thanks for great tips :)

  2. You are awesome. with spam comments my back up file size is 2.4MB. After deletion size became to 1.9MB.

    Thanks
    Sankar

  3. Thanks for the heads up.
    My blog is new and I can see that I am slowly building up the amount of spam that Akismet picks up.
    A great plugin that I check every day.
    With Google using page load speed as one of its metrics, it makes sense to keep the database size down.

  4. WordPress must have changed this because I have 2.8 and there is no spam in my database.

  5. I just wish there was a way from within the wordpress program to disable the storage of comments marked spam….!

    Oh well in the mean time, here is a PHP file I made to clean up the spam!

  6. I solved my problem by going in the database and deleting all the comments. The strange thing is I only say my regular and approved comments. Where are the filtered comments stored?

  7. My blog’s host (freehostia has blocked my site because their is spam comments containing key words that get you blocked automatically)Is there a way to delete these comments right from the database or I absolutely need to be able to log on to my blog and manally delete the spam comment. I also use the Akismet plugin. I’m so stuck because I can’t find anything on the subject. Any help would be apreceiated.

  8. Any tips for a person that desperately needs to use a backup file that is chock full of spam comments that weren’t finally zapped before the backup was made? PhpMyAdmin doesn’t even want to take the text of all of this and I know that my legit comments are not what is clogging it up. What are the syntax clues that the Akismet crap is starting?

  9. I delete Spam comments on a daily basis. I feel thats the best way to clear it, otherwise once it gets to more than 1000, you can’t even check if there was any genuine comment caught by Akismet.

  10. That’s a good tip. Those thousands of spam comments surely occupy too much space. Gotta delete ’em. Thanks for sharing…