Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Joyce Bruce]

I am interested if anyone has a set plan for deleting archived data, or using data compression.  We have not deleted anything since our install in 2013, and getting quite a lot of information stored on the system.  What are some of you doing monthly, quarterly, annually?

——————————
Joyce Bruce
Coloramics LLC dba Mayco Colors
Hilliard OH
——————————

[Blair Hurlbut]

Hi Joyce,

are you looking to run some of the deletion routines in NAV, such as delete empty registers?  Or are you looking to run the Date compression tools?

The Date Compression tool can be used and it does sum and insert a consolidated transaction to replace the details. This ensures that your values stay correct and the number of records is reduced thus making you data much smaller and allowing you to report on historical summary data. 

If you are running these tools I would recommend that you setup a test environment that is built from your Live or Production environment and run the processes there first. There are two reasons for this. First, you should time how long these processes take. They can take a lot of time to finish running and this will lock up the system and make it unavailable to users. If the processes take more than 8 to 12 hours then you will probably need to run these over multiple weekends depending on how many compressions you are planning to run.

The second reason is to validate that the process has run and the results are what you expect. If you are running this against inventory then you should run your inventory valuation reports in Production Prior to running the process then compare to same report in the test environment after running the process. 

Hope that helps. 

——————————
Blair Hurlbut
Catapult
Vancouver
——————————
——————————————-

[Paul Turner]

Here is an idea of some of the things we compress and truncate annually:

• Delete blank comment records (sales comment line, comment line)
• Delete orphaned Record Links
• Delete old Change Log Entries (we usually keep 1 year of these)
• Delete other old log table records from our various ISVs
• WMS Warehouse Entry record date compression.  NOTE – the standard date compression tool does not work for these entries; we had to build and thoroughly test our own (as of version 2009 R2; maybe newer versions handle this better).
• GL Entry date compression for entries older than 7 years (standard compression works fine here)
• Delete orphaned Reservation Entries (where the related doc no longer exists)
• Other general cleanup like cleaning up “dirty” cross reference values (ones that include special characters like CHR(13) and CHR(10)), messed up Lot Tracking entries (someone adjusted the wrong Lot number) etc.

——————————
Paul Turner
Liberty Mountain
Sandy UT
——————————
——————————————-

[Joyce Bruce]

Thanks for the input.

——————————
Joyce Bruce
Coloramics LLC dba Mayco Colors
Hilliard OH
——————————
——————————————-

[Joyce Bruce]

Thank You, I was guessing this to be a lengthy process. 
Good to know!

——————————
Joyce Bruce
Coloramics LLC dba Mayco Colors
Hilliard OH
——————————
——————————————-

[Geovanny Fuentes]

One thing to consider sometimes people will make a copy for references later on.
Incase you need it for an audit.

Like everyone mentioned but organize it.

1. Consider Compression or Deletion.
2. What table should I do it for.
3. How often.
4. Maybe do both for different reasons.
5. If you have a SQL person, try to produce a list of which tables have the most records and megabits size.
6. What is your comfort level of a database size: 2gb 10gb 30gb 250gb 500gb. etc.
7. Is speed a factor?
8. Which area runs slow, maybe it’s the object itself which requires fine tuning.

Good luck.

——————————
Geovanny Fuentes
San Diego CA
——————————
——————————————-

[Ian Ray]

If you have SQL Enterprise 2008+, SQL Standard 2016 SP1+, or Azure SQL, you can do “data compression” which has been reported to compress up to 80% with NAV. The main disadvantage of data compression is it taxes the CPU.

Date compression was mentioned above. I have never used this as I have read nothing but bad things about date compression on mibuso.

——————————
Ian Ray
Cypress Grove
Arcata CA
——————————
——————————————-