We are running Epicor 10.1.400.17, and have an SQL database that is ~850GB in size - the majority of this resides in the GLTrans, AP Invoice and AR Invoice tables. We run approximately 80k transactions daily. We currently have development environments where the dev team (myself included) are required to recompile the directives onto each machine in order to test the BPMs after having written some code.
Problem
This becomes a blocker at times, when there is more than one dev working on individual BPMs in the same environment (read: with the same database), then need to test as only one dev can ‘hold’ the BPMs until they’ve finished testing, and we’re trying to find a solution to this. Speaking with Epicor, they have mentioned that data is archived after 18 months - the issue here is, we’ve been on Epicor for a little less than that.
Solution?
We’re hoping that someone can suggest a way to dwindle down the database size so that we, as devs, can have a local copy of the database that we can work with. We’ve found that truncating the data down to the last 3 months caused issues in some forms, so this didn’t exactly work for us.
Does anyone have any suggestions? Maybe run in a similar environment?
Any thoughts or suggestions are greatly appreciated.
You will definitely need to make make a copy of your production database (850 GB) and restore it on your development server, then shrink the restored database. That will hugely reduce its physical size.
use EpicorDev905;
DBCC SHRINKDATABASE (EpicorDev905, TRUNCATEONLY);
If you don’t have enough space on the Dev Server, try first to shrink/truncate the log. then shrink database.
use EpicorDev905;
DBCC SHRINKFILE(‘EpicorDev905_log’, 0, TRUNCATEONLY)
From the SQL Magement Studio UI you can right-click on the database and do Tasks > Shrink > Database.
That should do it. Just be aware that this process also requires space before it can reduce the database physical size. That’s why you may want to shrink log file first.
WARNING! Do this on the development copy of the database. Never on production database.
You could have several “small” development environments that run off the Education Database, you can also take a backup of your current DB and restore it then run the purge and summarize feature to remove as much data as possible, dump your ChgLog tables, and the SysTask tables and any of the tables that aren’t essential then use that DB as your Master Dev DB and copy it around as needed.
