What is tempdb database in SQL Server?

Question

The TempDB database is one of the most important SQL Server system databases, that is used to store temporary user objects, such as the temporary tables that are defined by the user or returned from table-valued function execution, temporary stored procedures, table variables or indexes. 13 янв. 2020 г.

What is use of TempDB in SQL Server?

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.

Where is TempDB in SQL Server?

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.

What happens if 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.

Is TempDB shared between databases?

Since TempDB is shared across all databases and all connections in SQL Server, it might become a point of contention if not configured correctly.

Expert · Answer