Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Answered

SRSTMPDATASTORE and other "TMP" tables

Posted on by 17,786

I've notice that the SRSTMPDATASTORE table seems to grow forever.  I have records going back to the start of our production environment.  The same appears to be true with other TMP tables like SALESINVOICETMP, SALESCONFIRMHEADERTMP, SALESCONFIRMDETAILTMP, and probably a couple dozen other tables.

Is there a process built into AX to clean up these tables?  I would have thought the SSRS reports would do it when they were completed, but apparently not.

  • guk1964 Profile Picture
    guk1964 10,877 on at
    RE: SRSTMPDATASTORE and other "TMP" tables

    Its worth reading this link  msdn.microsoft.com/.../bb314749.aspx

    The retail statement posting, is an example of a report that uses  a large number of tmp tables. In such cases performance can be poor and that can result in records that don't get deleted, so its worth taking time to dig into this.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 287,995 Super User on at
    RE: SRSTMPDATASTORE and other "TMP" tables

    Hi Brandon,

    Sorry for my late response, but many thanks for your reply. Wel will continue and clean up this table as well.

  • Brandon Wiese Profile Picture
    Brandon Wiese 17,786 on at
    RE: SRSTMPDATASTORE and other "TMP" tables

    I have been clearing the table weekly for months with no ill effects.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 287,995 Super User on at
    RE: SRSTMPDATASTORE and other "TMP" tables

    Hi Brandon,

    Did you manually clear the SRSTmpDataStore table and possibly find any negative side effects when the records were deleted? Or was everything still working correctly? I do see a growing table at a customer running AX 2012 R2. SQL server is reporting missing indexes on this table due to the number of records.

  • sohaibcheema Profile Picture
    sohaibcheema 46,592 User Group Leader on at
    RE: SRSTMPDATASTORE and other "TMP" tables

    I believe certain reports may need to include manual fix by adding any line(s) of code, though its difficult to reproduce issue, on different installations of AX

  • Verified answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,786 on at
    RE: SRSTMPDATASTORE and other "TMP" tables

    I've located at least one place where a call to postReportRun(..) should be called but is not, and that seems to resolve at least some of the cases where form letter TMP records were not deleted.

    Now onto SRSTmpDataStore, for which I believe the records are used to marshall in-memory temporary tables into the report server context.

  • Brandon Wiese Profile Picture
    Brandon Wiese 17,786 on at
    RE: SRSTMPDATASTORE and other "TMP" tables

    Based on your reply, I did some further testing.  It does appear that rendering the Sales invoice to screen causes the postReportRun(..) cleanup to delete the SalesInvoiceTmp and related temporary data.  It also appears that some methods of running reports, such as through EP, do not properly cleanup the records.  I haven't tested every possible scenario yet, but it seems clear now the problem is related to how/where the report is run, at least in the case of SalesInvoiceTmp and other form letter documents.

  • sohaibcheema Profile Picture
    sohaibcheema 46,592 User Group Leader on at
    RE: SRSTMPDATASTORE and other "TMP" tables

    as per my thoughts for each report, data is being cleared at time RDP or Controller class is run.

    You can experiment something as under

    Go to class SalesConfirmationDP and search the text delete

    similarly go to Class SalesInvoiceDP

    also, pay a visit to class BankBillOfExchangeController >> ProcessReport

    All such reports are using pre-process approach by using SrsReportDataProviderPreProcess

  • Brandon Wiese Profile Picture
    Brandon Wiese 17,786 on at
    RE: SRSTMPDATASTORE and other "TMP" tables

    Of course they're not temporary tables by table type, or they wouldn't be sitting in the business database with data at rest at all.  But they do accumulate records over time that appear pointless and wasteful of storage, and I haven't yet found a built-in process that cleans them up either automatically or on demand.

  • Maqk Profile Picture
    Maqk 844 on at
    RE: SRSTMPDATASTORE and other "TMP" tables

    the tables you have mentioned by name are not temporary tables by table type, they would have TMP in name due to their usage in framework.

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... 287,995 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,610 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans