TRIGGER WARNING: I’m going to talk about deleting stuff from the database. Yes I know that’s bad
Our database size is currently 900gb. We’ve been on E9/10/11 since 2009 and ship about 700 lines a day so our biggest tables are PartTran, GLJrnlDtl but also the invoice and shipment tables. There are purge routines for PartTran and GlJrnlDtl but nothing for Invoice and Shipment. I’m sure I need to get rid of data because we have lots of speed issues, the database is just too large to do anything with. Out speed issues can often be traced to these tables because doing something like an index or statistics rebuild on them fixes the performance issue.
So i’ve got 2 crazy routes forward
- Start a new DB. Fresh and clean like a new implementation
- Consider deleting some things
Sure 1 is safer but from a business point of view equally (if not more) risky. One benefit of 1 is that it would remove many of the standard UD fields that E9 had in every table which consume lots of space but are mostly empty.
So after years of working with Epicor i’m thinking of deleting anything older than 7 years from the Invoice, cash and shipment tables. Other than loosing the deleted data i’m struggling to think what other impacts there could be. These don’t affect the GL once they’re dead. It might affect the rebuild credit balance routine? I’m working out a map of related tables and planning to use a cursor to go block at a time like many of Epicor’s fix programs do
Has anyone else been brave enough to do something similar. Anyone see any pitfalls i’ve overlooked. I have raised an Epicor idea to add this function btw.
Obviously i’m going to test first!
