Question

Database Purge and Summarize in Epicor 10. Thoughts?

  • 26 July 2021
  • 5 replies
  • 647 views
A
  • Anonymous
  • 0 replies

Hi,

Related topics seem to be from over 5 years ago, so I created a new one.

In short, our Epicor database is large and looking for solutions to save space.

 

The question is --

Has anyone used “Database Purge and Summarize” ?

 

This is in System Management→ Purge/Cleanup Routines→ Database Purge and Summarize.

If so:-

  • Is it reliable?
  • How often do you use it?
  • How long could it take?
  • What sections do or have you covered?
  • Any safety measures? Data backups, etc?
  • How much DB space can we save?

I understand that some of these questions are generic and can be compared to ‘how long is a peice of string’ argument. It really comes down to individual experience worth sharing.

 

I am going to look around EpicWeb to see if there is any content. If I find anything I will comment additional notes. It will be interesting to hear the do’s and dont’s. What has been learned.

To note, our biggest tables are below so we are likely to affect the Finance Department. We want to ensure we don’t “lose” anything that is important to them.

  • TranGLC
  • PartTran
  • GLJrnDtl
  • InvcDtl
  • OrderDtl

Thanks,

M

5 replies

M

I believe we did TranGLC which also took care of GlJrnDtl & Tran GLC.  We went with the summarize option.  We saved the info on the tables first in a DB so we could pull information if we needed to through SharePoint. 

The issue we ran into was that if there were old invoices open greater than the purge date we could not adjust them, apply cash, etc.  We have monthly payment options and this caused us issues because of how the posting rules work:  They go back to that original transaction and look to see what AR account the original invoice posted to and use that.  We had to get a datafix (?).  I will see if our IT guy can reach out to you directly.

A

Hi Martyn,

 I run the purge with Michelle. We purge the change log monthly, with a 2 year rolling cut-off. We just started doing a 3 year cut-off for Transactions (PartTran) and for Journal Details. Be careful about Journal Details (see Michelle’s comments above). We do NOT do the separate ‘Purge General Ledger History” since it seems that Journal Details summarizes the same. 

We backup the PartTran, TranGLC, and GLJrnDtl tables in a separate database so the history isn’t lost if we ever need it…

Here’s a few threads on EpiUsers about it: 

https://www.epiusers.help/t/purge-what-happens-to-fifo-layers-when-parttran-is-purged/74167

https://www.epiusers.help/t/database-purge-and-summarize-breaks-long-term-invoices/78957

https://www.epiusers.help/t/db-purge-and-summarize-process-e9-05-702/36882

Also, check out your SystemActivityLog - that may be filled with old transactions too, if you have it turned on.

Do the purge in a test environment to get an idea of the time (start with 1 month chunks then increase). It took about an hour per month for us, and I’d purge one table at a time. You’ll need to do a shrink and re-index on the SQL database afterward to realize that space. We didn’t, since our environment will grow to fill it over the year. I think we’re around 200GB now… If you submit your db to the Cirrus (upgrade services) team, you’ll get a list of your table size. https://upgradeservices.epicor.com/

Hope this helps!

Andris Skulte ERP Systems Analyst Engineering Services and Products Company / FarmTek / ClearSpan Fabric Structures South Windsor CT
G
Userlevel 2

Thanks for the posts here.  We have not run this but are considering it.  We went live in 2017 so we are getting to the point that the oldest 2 years are not really valuable.

One thing I have done is repurposed the default SSMS task to generate a report of our largest tables in our production database and output via e-mail a report each month. This alerted me that ABTwork was growing abnormally.  I had to get a data fix from support to take this down.  I think there are others that grow with no real valuable data.  Finding these and purging them is an easy win to bring down the database size.

Graeme Ross MBA CPIM CBAPDirector of Information TechnologyAdvance Engineered Products Ltd.Regina SK639-625-5018version 10.2.700.30
A

Just want to thank all of you for the replies so far. Great replies! Taking a step back to obsorb all this information.

 

A

I am considering this -- but for PartTran… which appears to be a tickbox option.

However, before I run this, I would like to backup the data it will affect. Will it just be the PartTran table affected or will we also need to back up other tables within that date range?

Reply


Terms & Conditions