Thread: Removing old orders to reduce database size

Hi, how can I remove all orders before a certain date from the store database. Our store database has grown very large and I would like to reduce it.
Also, it would also be great if I can remove all customers from the database that have not ordered from us within the last year or so.

Thanks

The monthly maintenance module in the admin might help you do the cleaning.

I have done the Monthly Maintenance many times, however none of the options let me permanently remove older orders from the store.
Is it possible to delete all the records of older orders - for example, all orders before January 1, 2010 - as it is just taking up extra memory and resources on the server.

The following SQL statements will clear those orders out. Make sure you have a good backup of your database before making changes to it!

NOTE: These statements assume that you want to remove ALL orders before "lastordernumbertokeep." If that is not the case, you may need to edit these statements.

DELETE FROM Orders WHERE OrderNumber < lastordernumbertokeep
DELETE FROM Orders_CustomCart WHERE OrderNumber < lastordernumbertokeep
DELETE FROM Orders_KitCart WHERE OrderNumber < lastordernumbertokeep
DELETE FROM Orders_ShoppingCart WHERE OrderNumber < lastordernumbertokeep

RUN THE SQL STATEMENTS ABOVE AT YOUR OWN RISK! BE CAREFUL! IF YOU DO NOT HAVE EXPERIENCE WITH SQL AT ALL, FIND SOMEONE THAT CAN DO IT FOR YOU.

Hi

Are you absolutely sure it's the orders table that is making your database size large? I'd be very wary of deleting old orders and would be looking at these areas first:

- Truncating the database transaction log
- Having done the above, run a Shrink task in Management Studio to reduce database/file size
- Running a report in SQL Server to see which tables are consuming the most space
- Looking at tables such as searchlog and profile to make sure they aren't excessive

Adam