What is Stored in TempDB? – Interview Question of the Week #271

Question: What is Stored in TempDB?

Answer: In my recent conversation with my client during they asked me if I know what is currently stored in their TempDb. If you run the following query you would get the detailed list.

SELECT tb.name AS [Temporary Object Name],
ps.row_count AS [# rows],
ps.used_page_count * 8 AS [Used space (KB)],
ps.reserved_page_count * 8 AS [Reserved space (KB)]
FROM tempdb.sys.partitions AS prt
INNER JOIN tempdb.sys.dm_db_partition_stats AS ps
ON prt.partition_id = ps.partition_id
AND prt.partition_number = ps.partition_number
INNER JOIN tempdb.sys.tables AS tb
ON ps.object_id = tb.object_id
ORDER BY tb.name

The query above will give us a result of something in the following line:

From the result, you can clearly figure out what actually is stored in your TempDB and based on that you can make a decision to either keep it or to remove it.

Please note that when you restart your SQL Server, your TempDB is anyway automatically reset to the original state. You should not store anything in your TempDB which you may need persistently needed in the future.

Let us see a very simple example of the same with the help of the latest sample database.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Exit mobile version