What is the use of TempDB 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 the role of TempDB in SQL Server?

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.

Why TempDB is growing in SQL Server?

Uncontrolled TempDB growth. There are many reasons for uncontrolled TempDB growth events. Much like your operating system has a page file to handle memory overflows, SQL Server uses TempDB like a page file. The most common occurrence of this is when a query “spills” to TempDB.

What are TempDB files in SQL Server?

TempDB is a global resource used by everyone and everything inside SQL Server. Think of it as a dumping ground for anything that doesn't fit in memory. It is always database_id number two in sys. databases. It's non-durable, meaning that the database is recreated every time SQL Service restarts.

Expert · Answer