Drupal : How To Get Table Sizes

Recently I was doing a deploy on a newly launched Drupal 8 site. As a rule while a site is in development I create a script to archive the site before deploy.

On a Drupal site the script backs up the site into separate files:

• Configs
• Site (core, theme and custom) files
• Content (user) files
• Database
• Repo

A typical set looks like below:

2020-02-06-1326.sitename.stage.db.sql

2020-02-06-1326.sitename.stage.configs.tar.gz

2020-02-06-1326.sitename.stage.files.tar.gz

2020-02-06-1326.sitename.stage.repo.tar.gz

2020-02-06-1326.sitename.stage.site.tar.gz

During the script run I noticed it was taking an unusual amount of time to do the DB dump and a quick check showed the available disk space decreasing at an alarming rate.

I knew that meant a something was growing at an unreasonable rate and need to find out where the issue was.

A quick and easy way to do this is a simple Drush command :


drush sql-query "SELECT table_name AS 'Table', ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)' FROM information_schema.TABLES WHERE table_schema = 'database name' ORDER BY (data_length + index_length) DESC;"

The above command will list the tables in order of highest to lowest.

In my case it was the watchdog table. It had grow to the size of 11GB in one day. A fellow developer had a piece of code that was generating a warning error at the rate of 10,000 an hour. A quick fix of the code and a flush of the watchdog table cleared the issue up.