temp tables in tempdb are not cleaned up by the system

Question

My question is similar to the one asked here: SQL Server tempdb rogue temporary tables

Since I don't have enough points to comment and the answer there doesn't address my concern, I can only ask a new question here.

Using SQL Search Tool in Visual Studio 2017 I can see the columns in the table and confirm that the temp table named #BBC835DE is indeed from a table variable, which is related to a stored procedure. I re-run the procedure without any problem, but this table still hangs on. How do I drop tables like this and clean up the tempdb? Thanks.

PS. Based on the result from SQL Search Tool, it seems that not all the tables named like #BBC835DE are from table variables, some are from the cursor declared in stored procedures too.

Answer 1

I believe this is to do with the plan cache. Just ran a little test on my end and I can replicate it.

To remove the table from tempdb DBCC FREEPROCCACHE works, obviously target the plan handle for the stored procedure.

You could do this but it would just come back again and freeing the cache all the time generally isn't the best idea.

If you're concerned about it taking up space you can run this query I wrote for a monitoring tool that should work. On my tests it always shows 0KB after the SP has finished. This leads me to believe it just keeps the shell of the table there for some internal use. So it seems harmless to me.

USE tempdb;
WITH KB
AS (SELECT t.name,
           SUM(a.used_pages) * 8 AS KBUsed,
           p.rows AS [Rows]
    FROM sys.tables t
        INNER JOIN sys.partitions p
            ON t.object_id = p.object_id
        INNER JOIN sys.allocation_units a
            ON a.container_id = p.partition_id
    WHERE t.is_ms_shipped = 0
    GROUP BY t.name,
             p.rows
   ) SELECT * FROM KB