SQL Server: Forcing cleanup of freed internal objects in tempdb to free up disk space reserved for them in an open session

Question

I have a large cursor database query that runs inside a stored procedure in a job. This makes tons of calculations for the volume of market data in a cycle throughout the day. Each such iteration combines fragments of historical time series from a disk, pushes them into temporary tables with appropriate indexing, combines them into several trunsformations with intermediate results, and saves disk output calculations. At the end of each loop, I drop (basically) or crop all the temporary tables to free up the pages of user objects inside tempdb and get a namespace for the next iteration.

My problem is that after each cycle, all the internal objects that are created to execute the request and run them in tempdb - save free disk space, even if they are freed after transactions. And this is added to each cycle, as the next piece of new internal objects is turned on the disk.

This leads to a constant tempdb grouth, all accumulating the reserved space associated with new and new remote internal objects. DB Engine releases / reduces (independently) these tons of wasted disk space only after the session is closed, when proc completes the cycle.

I can solve the problem by reducing the number of cycles in each task, just run it again. But I would like to get a complete fundamental solution: I need a command or some kind of trick inside the session to force garbage collection upon my request to completely clean / destroy completely freed up internal objects and free up the reserved space for them on the tempdb disk. Googling days didn't help. Help people!

Answer 1

We have exactly the same problem:

• lengthy recalculations are performed every night;
• uses many temporary tables to use parallel execution plans

To fix this problem, we simply divided the processes into small processes, each performed in a separate session, but chained (to avoid blocking problems) - when the first part is executed, it starts the next part, then after it runs it starts the next.

For example (if you have a way to relate your calculations), you can split your loop iterations to separate a procedure call with different parameters. It is performed in different sessions, when each of them is completed, the pages will be released.