Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / How to truncate transa...
Microsoft Dynamics RMS (Archived)

How to truncate transactions from 2013 below

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Mabuhay!

Hi! We are planning to restore our database to a new server. Can you help us truncate those entries from 2013 below?

If we run this one transactions will not show even it was 2008 above.

delete from transactionentry from transactionentry te join [transaction] t on te.transactionnumber = t.transactionnumber and year(t.time) <= 2007
delete from tenderentry from tenderentry te join [transaction] t on te.transactionnumber = t.transactionnumber and year(t.time) <= 2007
delete from [transaction] where year(time)<=2007

delete from orderentry from orderentry oe join [order] o on oe.orderid = o.id and year(o.time) <=2007
delete from [order] where year(time) <=2007

delete from purchaseorderentry from purchaseorderentry pe join purchaseorder p on pe.purchaseorderid = p.id and year(p.datecreated) <=2007
delete from purchaseorder where year(datecreated) <=2007

delete from inventorytransferlog where year(datetransferred) <=2007

Thank you,

Chris

 

*This post is locked for comments

  • Suggested answer
    Antonijo Todorovik Profile Picture
    Antonijo Todorovik 4,025 on at
    RE: How to truncate transactions from 2013 below

    Hi Christopher,

    You will have to SHRINK your database after the deleting of data. This could be done in  several ways, please see this link: msdn.microsoft.com/.../ms189035.aspx

    Look for your SQL version and my advice would be to SQL Server Management Studio. Once You shrink your database, open the properties of the database and put some limits on database growth...

    And at the end, try this FIRST in TESTING ENVIRONMENT, with a database copy!!!

    Best regards, Antonijo

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to truncate transactions from 2013 below

    Mabuhay! Hi Sir A,

    Done deleting that data but what I've notice was from previous datasize it is still 90GB do i miss something. Can I get this database (HQ) at least 50GB or less? I deleted transactions   from 2013 below.

    Thanks,

    Chris

  • Verified answer
    Antonijo Todorovik Profile Picture
    Antonijo Todorovik 4,025 on at
    RE: How to truncate transactions from 2013 below

    Great.

    Then, please verify my answer, so other forum users can have use of it. Also, once you have done your work, please publish your results as a knowledge base for the forum.

    Best regards, A.

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to truncate transactions from 2013 below

    Mabuhay Sir BR, A.

    Hi! Thanks for this tip, we also see the link for the purging and will try first for the test db and ig it will going to work we are going to purge the old transactions. But it will be visible for the original db so we can look back again for the trasanctions its like were just archieving so we can save more memory on exisiting now.

    Chris

  • Verified answer
    Antonijo Todorovik Profile Picture
    Antonijo Todorovik 4,025 on at
    RE: How to truncate transactions from 2013 below

    Hi Christopher,

    Well, knowing that direct deleting from the RMS (or any SQL) database can be dangerous and You can obviously loose your data, corrupt the database, etc., etc.,., I would suggest doing this next few options:

    1º) whatever you do or choose to do, always work first in test environement!! Do several tests, not only one, until You are sure that you are on right track...

    2º) why don't you contact your RMS Partner, through them you can get some tools/add-ons that can do this for you, like for example Retail Realms Archiving?? I'm not completly sure, but if you are on BREP there are some add-ons from Retail Realm that you can get for free, not sure if Archiving is part of it, but maybe...

    3º) Talk to an RMS Partner with more experiance, that can do this for you. Someone who have done it before...

    4º) If You decide to do it on your own (look at 1º again), search this forum, similar questions ware answered several times, maybe you can get some knowledge from there... try this for example: community.dynamics.com/.../87420

    Hope this helps, do not hesitate to contact me if you need more help...

    BR, A.

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans