Hi,
I'm a DBA working with AX for the first time.
I've noticed that application creates a lot of TempDB tables, and due to a lot of the code it doesn't delete most of the tables it creates (i.e the devs never built cleaning up routines in the code). This is creating a big problem because these tables start to eat into the memory of SQL, and take memory that could be used by the buffer pool (i currently have 300k temp tables and growing).
I spoke to one of the AX devs who advised that if AX needs to use a temp table it will create one, and it won't use a previously created table. So from my understanding it is always creating temp tables when required, does what it needs to do, and finishes, doesn't drop the table, rinse and repeat.
But what i have noticed when looking at queries throughout the day, is that some queries are using temp tables that were created weeks ago.
My main questions are, if i delete one of these tables while it is not in use, and then a query runs that would've used that tempdb table. Will AX create a new table because it can't find the previous one, or would there be some error when the query runs, as it can't find the temp table it expects to be there?
The end goal for all of this is to create a routine that cleans out TempDB tables on a nightly basis.
Thanks
