In a Service Broker queue, when one message is proceed over time, we are executing a stored procedure which is performing some heavy operations using temporary tables.
I guess that the sp_WhoIsActive is showing summarized values for tempdb allocations and CPU usage, as the queue is using one session.
The question is, are the allocations released when the current stored procedure completes?
Please see the Microsoft BOL for Temporary Tables:
Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE:
A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.
All other local temporary tables are dropped automatically at the end of the current session.
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
Once the TempTable is dropped, the space it was consuming (and any resources reliant on it) is released (internally). If the TempTable caused your TempDB data file to grow on disk, even though the space is released internally in SQL Server, it won't be released back to the disk in the OS (just like any other database's data file). It will be re-used internally by SQL Server for other TempDB operations though.
Restarting SQL Server or running a SHRINK command are ways to release the internal unused space back to the disk for the OS, if that is of concern.
