Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Answered

Table records in Ax2012

Posted on by

Hi Folks,

I have some tables where records are crossing over 7 million. I cannot delete any of these records & currently have no data warehousing in place.

Please suggest

1.What is the max number of records Ax table can handle before it starts impacting performance.

2.Considering i cannot delete any record, should the best alternative way be to introduce data warehousing & move older data to other db periodically, if so then how user will access thsi old data in Ax once moved to data warehousing db.

3.I could only find 1 out of box clean up job, please let me know if there are any out of box cleaning job for list below

TableName RowCount Out of box cleanup available Out of box job name
[dbo].[WORKFLOWTRACKINGARGUMENTTABLE] 7 millon plus Could not find  
[dbo].[WORKFLOWTRACKINGCOMMENTTABLE] 7 millon plus Could not find  
[dbo].[WORKFLOWTRACKINGTABLE] 7 millon plus Could not find  
[dbo].[GENERALJOURNALACCOUNTENTRY] 5 millon plus Could not find  
[dbo].[EVENTCUD] 5 millon plus Could not find  
[dbo].[EVENTINBOXDATA] 4 millon plus Could not find  
[dbo].[EVENTINBOX] 4 millon plus Yes Notification cleanup
[dbo].[VENDSETTLEMENT] 4 millon plus Could not find  
[dbo].[VENDTRANS] 4 millon plus Could not find  
  • Verified answer
    Yuji Imaoka Profile Picture
    Yuji Imaoka on at
  • Mav Profile Picture
    Mav on at
    RE: Table records in Ax2012

    @Sukrut Can you please share what maintenance plan you had for tables having millions record.

    @Sergei Minozhenko based alert batch jobs calls this class EVENTJobCud  & i dont find any method in that class which would delete records from EventCUD table, please correct me if i m wrong.

    @Will Wu Db size is 340 gb

    At times some workflow will get stuck and wont process until i restart the AOS's hence was wondering if this is due to more than 6 million data in workflow tables.

    Will trying reindexing or db synch  or any other option under sys admin >periodic>database >sql administration work help.

  • WillWU Profile Picture
    WillWU 22,344 on at
    RE: Table records in Ax2012

    Hi Mav,

    How about current database Size?

    7 million records are not particularly large.How is the database performance now?

    See the blog:

    www.axpulse.com/.../

  • Suggested answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,083 on at
    RE: Table records in Ax2012

    Hi Mav,

    EventCUD table should be cleaned by "Change based alerts" batch job.

    docs.microsoft.com/.../create-alerts

  • Mav Profile Picture
    Mav on at
    RE: Table records in Ax2012

    @sukrut

    Can you please share what maintenance plan you had for those GL tables.

  • Suggested answer
    Sukrut Parab Profile Picture
    Sukrut Parab 71,643 Moderator on at
    RE: Table records in Ax2012

    I am not aware of any limit defined on row count of the table. I have seen more than 35 million records in some GL tables and we did not face any major performance issue because the maintenance plan for those table was in place.

    If you plan to move that data to data warehouse then you can provide reports to user , if they want to see that data.

    If there is no standard clean up then you have to create your own clean up, however you have to  be cautious  while working with tables like vendtrans and vendsettlement.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,459 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,783 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans