Going through a nopCommerce project with a 40GB database, I found the majority of the space was allotted to a very large Customer table. To fix this, I decided to delete Customer data with the following criteria:
- No username, email, and password data
- Not a system account
- Doesn’t reference a Shipping and Billing address
- Last activity recorded a month from today’s date
I considered the customers having shopping cart items as well, but found that most of the junk data had one shopping cart item – I think this comes from a robot regularly adding specific items to a cart to check price.
Here’s the SQL used:
Once this is done, make sure to shrink the database to reclaim the space gained by clearing out the data.