Cleaning up Customer Data in nopCommerce

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.