Our company is about 3 years into using Epicor and the DB is over 500 GB (on premise). We don’t have any configuration problems like excessive logging. I’m looking into options for managing the database size. In another 3 years, we’ll be well over a Terabyte if I don’t do anything.
I will definitely be using Database Purge and Summarize.
There’s an sql option that can be set on tables and indexes:
DATA_COMPRESSION = PAGE
Has anyone implemented page level compression? If so, how did that go?
As far as I can tell, neither the documentation nor the PDT tool specify a specific compression measure. What are you doing now? Have you run sp_estimate_data_compression_savings with the proposed change?
We haven’t made any changes yet.
I didn’t run sp_estimate_data_compression_savings, but we are using the maintenance solution from https://ola.hallengren.com/
and it can be used to generate a similar calculation.
We would be able to reduce the size of our database by about 270 GB with page level compression. I think there’s a lot of potential here because Epicor has indexes on pretty much everything and from what I understand those can compress nicely.
You could also just buy new drives… Storage is dirt cheap! You can get 2TB SSDs for 200$…
Don’t forget compression isn’t free, latency will go up and so will CPU usage…
That seems pretty high unless you are high transaction many users environment…
I’d be curious to see what SQL Disk Usage and table size reports look like (these are standard reports you can find if you right click the db in SSMS).
I would only be enabling compression on your non production instances.
That being said your data is over 3 years old, have you considered database purge and summarize. It will only purge anything 18 months or older.
And… Dare I say TEST, TEST, TEST and have a good plan if you need that access to that older data.
When you mean you are on-prem do you mean physically or running on VMS in some self managed cloud environment?
That’s my first thought! User interaction alone feels implausible though… Assuming 100wpm equivalent * 40 hours a week is stored per user, and the database triples that because normalization schmormalization, hitting 500GiB in 3 years would require roughly 4,000 users.
Something else is going on. Storage optimization could help for a bit, but at this rate it’s worth chasing down what’s going on and moving it out of ERP. You’re still on the hook for storage of whatever that is, but at this rate it seems there’s a lot being stored on ERP that should be on a file system where it’s trivial to archive or remove when it’s aged past usefulness.
Is the database being used for image storage? Is some high volume machinery logging its every move in ERP? Maybe the total includes unmaintained logs, but that’s still a huge stretch.
I think so too… @Potato we’d love to see those SQL table size reports!
We had a similar issue a few years back and it turned out to be one of the Configurator tables growing out of control because we missed a checkbox setting called “Shrink Field Properties” or something like that. Basically without that box checked, these tables would store giant xml blobs of configurator input field properties for every single quote/order line configuration. With the box checked it only stores field properties that differ from a default value.
We are on-prem, as in self-managed VMs.
Here is a screen of our largest database tables. I believe that the large database size is caused by our setup where we use PCID tracking on a large number of parts. We manufacture at multiple locations, then transfer to 20 something stores where the parts are sold.
Your indexes look pretty large as well. What is your fragmentation like? Also noticed you have a UD on Part Tran…
The database fragmentation is really bad. I do have the tools in hand to get it cleaned up. So I’ll go take care of that and then reassess the situation.
The UD tables on PartTran are part of our Precise integration (Point of Sale software)
Fair enough…
