Drupal - Which tables are safe to clear?

Drupal 7 tables that can be excluded

Here is a list of tables in Drupal 7 that you can either clear (to reduce the database size) or safely exclude to do a migration (as in the question about How to reduce the locally exported database size to get around my server import limit?):

  • accesslog
  • batch
  • all cache related tables, such as:
    • cache*
    • cache_block
    • cache_content
    • cache_filter*
    • cache_form
    • cache_calendar_ical
    • cache_menu*
    • cache_page*
    • cache_views
    • *_cache, such as features_cache or views_data_object_export_cache
  • ctools_views_cache
  • ctools_object_cache
  • devel_queries
  • devel_times
  • flood
  • history
  • queue
  • various search_* tables, such as:
    • search_dataset
    • search_index
    • search_keywords_log
    • search_total
  • semaphore
  • sessions
  • watchdog
  • webform_submitted_data

Usually tables such as search_index and watchdog use a lot of database space, so just eliminating those 2 tables can make a huge difference already.

Other tables that might be excluded

Check the size of your remaining tables and identify which one of them are the biggest in size.

Typically you might find session tables for which no cleanup procedure is in place. Such tables you can probably also exclude.

Module Backup and Migrate

To further reduce the challenge as detailed in "How to reduce the locally exported database size to get around my server import limit?", look at the Backup and Migrate module also. Here is a quote from its project page (bold markup added here):

Back up and restore your Drupal MySQL database, code, and files or migrate a site between environments. Backup and Migrate supports gzip, bzip and zip compression as well as automatic scheduled backups.

With Backup and Migrate you can dump some or all of your database tables to a file download or save to a file on the server or offsite, and to restore from an uploaded or previously saved database dump. You can choose which tables and what data to backup and cache data is excluded by default.

And there is even more: if your local environment (e.g. Win or Mac) differs from the OS that the server of your hosted website is running (like Linux), then these differences between OS-es imply potential extra challenges. I've had good experiences with the Backup and Migrate module between different OSes, which didn't cause any problems (worked fine) in situations where the typical MySql export / import failed before.


Use the backup & migrate module, it comes with good defaults for skipping not necessary data. By default it generates a DB backup without cache, watchdog and some other tables.

If this does not help have a look with phpMyAdmin and tell us which tables have a lot of entries.


In my experience, I purge all of the "cache_*" tables.

  • plus "watchdog" if I don't care about past Drupal logs
  • plus "accesslog" if I don't care about logged-in users
  • plus "search" if I don't care about indexed nodes contents

Tags:

Database

Recent Posts