Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP forum
Answered

Remove Transaction and Distribution history

Posted on by 5

Our current Dynamics GP is under GP10 version and we want to purge all the historical records five years and below before we migrate it to a newer version. Our record is from 2008 to current year. Our main concern was the current database size is around 4TB. We cannot use the suggested Company Data Archive tool as there are a lot of user tables that was created.

We would like to know what will be the best solution practice for this.

If in case we use the GP utilities in removal of historical record for Sales, Financial, Inventory and Purchasing; what will be the steps in doing it.

Thanks

Categories:
  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Remove Transaction and Distribution history

    You are welcome. You have quite the job on your hands. Keep us posted and let us know how it goes.

  • spiderman Profile Picture
    spiderman 5 on at
    RE: Remove Transaction and Distribution history

    Thanks to both of you, Tim and Richard

  • Verified answer
    Tim Wappat Profile Picture
    Tim Wappat 5,701 on at
    RE: Remove Transaction and Distribution history

    I can NOT recommend that.

    I've done this work before and its painstaking and risky (but with lots of time and care possible). You end up with some pretty sophisticated SQL scripts that have to encapsulate the GP business logic that you are aware of (and the fear is that logic which you are unaware of and ultimately breaks the application). This would leave you vulnerable from a formal GP technical support perspective too (should have used the GP interface).

    There are many dependencies between records, so simply deleting based on date does not work in many cases, as those old records may be required for something else to work properly. Indeed all may look fine until you next run check links or reconcile where GP then proceeds to delete what you regard as valid data -due to orphan dependencies.

    The tables themselves have SQL triggers on them that fire as you delete data, so other tables can have data deleted from them when you delete a record elsewhere. So copying tables must be considered carefully as triggers may be pointing at the wrong copy of the data.

    I could go on...

    Its a minefield, even if you have years of experience in GP schema and application.

    Your own tables are easier, assuming you understand your own dependencies. In that case yes your plan could work.

    Sorry but there are no easy ways forward.

    Also I agree with what @RichardWheeler said.

    Tim

  • Verified answer
    Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Remove Transaction and Distribution history

    How many custom tables do you have? The end of the calendar year will be here soon. On 12/31/2019 can you take a backup and either copy it to an archive company under this SQL instance or move it to another instance? There are Remove History options in GP under the various modules that are date sensitive. The Company Data Archive product is still a good choice. This would cover all the GP tables leaving only the custom tables to you. In this case you may want to try shrinking both the data and log files. I normally do not recommend this but for such a bulk delete it would make sense You would recover much space. Of course before you do anything perform a backup. Perhaps you could try this in a test environment first?

  • spiderman Profile Picture
    spiderman 5 on at
    RE: Remove Transaction and Distribution history

    Thanks Tim.

    Someone suggested that:

    1. Create a temporary table same as the live table structure (e.g. SOP30200_Arch)

    2. Create Index on temporary table same as live table.

    3. Create a SQL JOB or manually transfer the latest 5 years records (2014-2019),  from live table to temporary table

    4. Once all the latest records are copied to the temporary table; rename the live table to a temporary name and temporary table to the live table.

    5. The same process will be followed for further tables.

    Will that be feasible?

    My suggestion was, if in case they planned to procure a new ERP system, migrate all the necessary data needed to the new system, then leave the Dynamic GP as  is as an Archive System.

    Will the suggestion be a best solution?

  • Verified answer
    Tim Wappat Profile Picture
    Tim Wappat 5,701 on at
    RE: Remove Transaction and Distribution history

    There is no magic wand. One of the debts you accumulate if you add your own tables is having to write maintenance scripts to keep them healthy and trim history.

    The Company Data Archive Tool is the best solution out there for trimming the base product, otherwise you are correct that you need to go through each module removing history, this is not difficult and will be in the help pages for those windows or the user guide. 

    Whatever happens, you will need to deal with your custom tables which could be a significant project in itself, but you may find you don't need them all any more?

    Good luck with the project.

    Tim.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,537 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,520 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Product updates

Dynamics 365 release plans