What is taking up TempDB space?

Question

User temdpb usage is related to creating and populating @table variables or #temporary tables and explicitly populating them. System tempdb usage is related to large operations such as hash joins or sorts which cannot fit into memory and as a result must spill into tempdb. 27 июл. 2020 г.

What is consuming space in tempdb?

This means that a temporary table consumes space in tempdb as long as the session is still open (or until the table is explicitly dropped), while a table variable's space in tempdb is deallocated as soon as the batch is ended.

How do I free up space on tempdb?

To remove additional files in tempdb, use the ALTER DATABASE command by using the REMOVE FILE option. Use the DBCC SHRINKDATABASE command to shrink the tempdb database.

Why is tempdb growing so large?

Tempdb growth is mainly due to poor performing queries, so you can use SQL Profiler and probably filter on Duration to determine if there are any stored procedures that are taking more than x seconds to execute. You should only be interested in the SP Event classes.

How do I see what is in tempdb?

Drill down into the SQL Server instance, Databases, System Databases, and right click on the tempdb database. Select Properties in the pop-up menu. The Database Properties window will open. As you can see in the image below, the SQL Server 2019 container I downloaded is configured with four tempdb data files.

Expert · Answer