What happens when TempDB is full?

Question

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.

What should I do if SQL Server tempdb is full?

When investigating a TempDB issue like this, most simply restart the SQL Server instance. It's easy to see why they do – the issue quite often locks up completely, and if a customer wants their server to work again ASAP, then a restart is almost inevitable. A restart will tackle the symptom, but not the cause.

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.

How do I free up space on tempdb?

All tempdb files are re-created during startup. However, they are empty and can be removed. 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.

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.

Expert · Answer