You’ve cleared revisions, drafts, transients, optimised & repaired your database, NOTHING WORKS and your database is still 10GB for no reason. Sounds familiar?
Here's the fix
So I did two things that led to the fix – I had wanted to do more testing but at point of writing I have already optimised all my sites with this problem. I’ll update this post in a month if it reoccurs and I find a proper fix.
If you encounter this same problem, try one of these, see if it works (and let me know!):
phpMyAdmin > wp_options > Operations > Analyze Table
OR
Install WP Optimize plugin, dont’ need to do anything else, just wait 10 mins.
The result should look like this (screenshot taken from WP Optimize > Tables screen):
Why did this happen in the first place?
Honestly I have no idea as I’m not a database expert. If you find the real cause please let me know. I stumbled upon this fix entirely by accident, and there are no solutions from my many hours of Googling either. But again, I’m not a professional programmer so I don’t know what to really look for.
Hope this solves it for you. Good luck!