US - Massachusetts - Boston - AX

Expand all | Collapse all

TempDB creating Regular Tables in SQL

  • 1.  TempDB creating Regular Tables in SQL

    Posted 15 days ago
    We've been dealing with a TempDB problem that doesn't seem to fit the way Microsoft states the TempDB should act. Microsoft states that the TempDB space (ie. files) will drop when the session goes out of scope. But we are seeing TempDB tables that are weeks old. The only way we can stave off the TempDB filling up and crashing the AOS is to drop the multiple AOS's and restart the system. ​​

    My question is "why does the TempDB contain SQL Regular tables?" And why aren't they being dropped? I have yet to see a post anywhere that addresses this question.

    Problem Statement:

    • About every 2 weeks the entire AX Production Environment needs to be rebooted.
    • All user sessions must end.
    • All AOS services are stopped
    • The database servers are stopped
    • Then the database servers, AOS servers and Client server are brought back on-line.
    • The process takes about 1 hour.

     

    Analysis

    • The cause is related to the size of the tempDB. It consumes all available disk space and causes sessions to fail.
    • Tens of thousands of Regular tables in the tempDB are dropped when the AOS services are stopped.
    • Regular Tables in the tempDB begin with the letter t. These are not temporary tables even though they are in the tempDB.
    • SQL Server drops Temporary tables when the code block ends. However, Regular tables are not.

     

    • Dynamics AX creates a Regular table in tempDB the first time a table is Instantiated and again every time the same code block Instantiates the table. Hence, there may be many tables created in tempDB with the same definition.
    • Other users may process the same table. Dynamics AX will reuse the table if it is available. Otherwise, it will create another table in tempDB. Creating even more tables with the same definition.

     

    • These Regular tables are not deleted by Dynamics AX when the code block ends or when the user logs off. The table remains. Some of them are weeks old.


    ------------------------------
    John Jones
    Amphenol Corporation
    Nashua NH
    ------------------------------


  • 2.  RE: TempDB creating Regular Tables in SQL

    Posted 14 days ago

    I think there is wrong with the way sql was setup. Check that all the necessary traces are setup properly in the sql.

    Regards,
    Lawrence


    Sent from my Sprint Samsung Galaxy Note8.





  • 3.  RE: TempDB creating Regular Tables in SQL

    Posted 14 days ago
    The application has been working for over a year without any other database issues except the TempDB filling up with undeleted files. ​We've had Microsoft look into it but they don't see any issues. We are looking for solutions that can explain why the TempDB acts this way despite the documentation saying it should clean up after itself.

    ------------------------------
    John Jones
    Amphenol Corporation
    Nashua NH
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role