What does shrinking TempDB do?

Question

It performs the database level shrink, and you get the following output. You can check the size of the data and log files for the database using tempdb. sys. database_files. 15 авг. 2019 г.

Is it okay to shrink TempDB?

In SQL Server 2005 and later versions, shrinking the tempdb database is no different than shrinking a user database except for the fact that tempdb resets to its configured size after each restart of the instance of SQL Server. It is safe to run shrink in tempdb while tempdb activity is ongoing.

Does TempDB affect performance?

Waiting for TempDB to grow can be a cause of performance problems and enabling Instant File Initialization makes it possible to quickly grow TempDB data files. Using multiple TempDB files uses more storage bandwidth, reduces file contention, and adds magical pixie dust to your queries.

Why does TempDB grow 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.

What happens when TempDB is full?

The TempDB database is special in many ways, but an interesting aspect is that when its files automatically grow when they become full, this growth is not persisted and will be undone on the next restart of the SQL Server service.

Expert · Answer