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
------------------------------