What is taking up space in tempdb?

Question

Indexes and statistics on temporary tables also consume space in tempdb, while indexes and statistics on table variables don't. This is simply because you cannot create indexes or statistics on table variables. Well, you can create indexes as part of the table declaration, but this is not common. 13 мая 2020 г.

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.

What causes TempDB to fill up?

Most of the time tempdb fills is related to when a user kicks off a long running query and decides to get a cup of coffee, or go out to lunch.

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.

What objects are in TempDB?

Overview. The tempdb system database is a global resource that holds: Temporary user objects that are explicitly created. They include global or local temporary tables and indexes, temporary stored procedures, table variables, tables returned in table-valued functions, and cursors.

Expert · Answer