SQL Server: force cleanup of deallocated internal objects in tempdb to release disk space reserved for them within an opened session

Question

I have a large cursor bases query which runs within a stored procedure under a job. It makes tons of calculations for bunches of market data in a loop all day long. Every such iteration pools pieces of history time series from a disk, fatches it to temporary tables with appropriate indexing, joins them in a number of trunsformations with intermediate results and stores calculation output to disk. At the end of each loop I drop (mostly) or truncate all temporary tables to deallocate pages of user objects inside tempdb and get namespace ready for the next iteration.

My problem is that after each cycle all internal objects, which DB Engine creates for query execution and dump them to tempdb, - keep disk space reserved for them even being deallocated after transactions commit. And it adds up on every cycle as the next bunch of new internal objects is swiped to disk.

It leads to permanent tempdb grouth, all of accumulating reserved space related to new-and-new deallocated internal objects. DB Engine releases/shrinks (whatever) these tons of wasted disk space only after session closes when proc finishes it's cycles.

I can overcome the problem by reducing number of cycles in each job run, just start it again. But I would like a complete fundamental decision: I need a command or any kind of trick inside a session to force rubbish collection on my demand to clean up / kill deallocated internal objects completely and release tempdb disk space reserved for them. Days of googling did not help. Folks, help!

Answer 1

We have exactly the same issue:

1. time consuming recalculations are executed every night;
2. a lot of temporary tables are used in order to used parallel execution plans

In order to fix the issue, we've just divided the processes to small processes executing each in separate session, but chained (in order to avoid blocking issues) - when the first part is executed, it fires up the next part, then after it is executed, it fires the next one.

For example (if you have a way to chain your calculations), you can break your loop iterations to separate calling of procedure with different parameters. Executed in different sessions, when each of them is finished the pages will be released.