---
title: "tempdb database | Microsoft Docs"
description: This topic provides details about the configuration and use of the tempdb database in SQL Server and Azure SQL Database.
ms.custom: "P360"
ms.date: 09/16/2020
ms.prod: sql
ms.prod_service: "database-engine"
ms.technology: 
ms.topic: conceptual
helpviewer_keywords: 
  - "temporary tables [SQL Server], tempdb database"
  - "tempdb database [SQL Server], about tempdb"
  - "temporary stored procedures [SQL Server]"
  - "tempdb database [SQL Server]"
ms.assetid: ce4053fb-e37a-4851-b711-8e504059a780
author: "stevestein"
ms.author: "sstein"
monikerRange: "=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current"
---
# tempdb database

[!INCLUDE [SQL Server Azure SQL Database](../../includes/applies-to-version/sql-asdb.md)]

The `tempdb` system database is a global resource that's available to all users connected to the instance of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] or connected to Azure SQL Database. `tempdb` 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.  
- *Internal objects* that the database engine creates. They include:
  - Work tables to store intermediate results for spools, cursors, sorts, and temporary large object (LOB) storage.
  - Work files for hash join or hash aggregate operations.
  - Intermediate sort results for operations such as creating or rebuilding indexes (if `SORT_IN_TEMPDB` is specified), or certain `GROUP BY`, `ORDER BY`, or `UNION` queries.

  Each internal object uses a minimum of nine pages: an IAM page and an eight-page extent. For more information about pages and extents, see [Pages and extents](../../relational-databases/pages-and-extents-architecture-guide.md#pages-and-extents).

  > [!IMPORTANT]
  > Azure SQL Database single databases and elastic pools support global temporary tables and global temporary stored procedures that are stored in `tempdb` and are scoped to the database level. 
  >
  > Global temporary tables and global temporary stored procedures are shared for all users' sessions within the same SQL database. User sessions from other SQL databases can't access global temporary tables. For more information, see [Database scoped global temporary tables (Azure SQL Database)](../../t-sql/statements/create-table-transact-sql.md#database-scoped-global-temporary-tables-azure-sql-database). [Azure SQL Managed Instance](/azure/sql-database/sql-database-managed-instance) supports the same temporary objects as does SQL Server.
  >
  > For Azure SQL Database single databases and elastic pools, only the master database and `tempdb` database apply. For more information, see [What is an Azure SQL Database server?](/azure/sql-database/sql-database-servers-databases#what-is-an-azure-sql-database-server). For a discussion of `tempdb` in the context of Azure SQL Database single databases and elastic pools, see [tempdb database in Azure SQL Database single databases and elastic pools](#tempdb-database-in-sql-database). 
  >
  > For Azure SQL Managed Instance, all system databases apply.

- *Version stores*, which are collections of data pages that hold the data rows that support features for row versioning. There are two types: a common version store and an online-index-build version store. The version stores contain:
  - Row versions that are generated by data modification transactions in a database that uses `READ COMMITTED` through row versioning isolation or snapshot isolation transactions.  
  - Row versions that are generated by data modification transactions for features, such as online index operations, Multiple Active Result Sets (MARS), and `AFTER` triggers.  
  
Operations within `tempdb` are minimally logged so that transactions can be rolled back. `tempdb` is re-created every time [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. 

`tempdb` never has anything to be saved from one session of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] to another. Backup and restore operations are not allowed on `tempdb`.  

## Physical properties of tempdb in SQL Server

The following table lists the initial configuration values of the `tempdb` data and log files in SQL Server. The values are based on the defaults for the `model` database. The sizes of these files might vary slightly for different editions of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)].  
  
|File|Logical name|Physical name|Initial size|File growth|  
|----------|------------------|-------------------|------------------|-----------------|  
|Primary data|tempdev|tempdb.mdf|8 megabytes|Autogrow by 64 MB until the disk is full|  
|Secondary data files|temp#|tempdb_mssql_#.ndf|8 megabytes|Autogrow by 64 MB until the disk is full|  
|Log|templog|templog.ldf|8 megabytes|Autogrow by 64 megabytes to a maximum of 2 terabytes|  
  
The number of secondary data files depends on the number of (logical) processors on the machine. As a general rule, if the number of logical processors is less than or equal to eight, use the same number of data files as logical processors. If the number of logical processors is greater than eight, use eight data files. Then if contention continues, increase the number of data files by multiples of four until the contention decreases to acceptable levels, or make changes to the workload/code.

> [!NOTE]
> The default value for the number of data files is based on the general guidelines in [KB 2154845](https://support.microsoft.com/kb/2154845/).  
  
### Moving the tempdb data and log files in SQL Server

To move the `tempdb` data and log files, see [Move system databases](../../relational-databases/databases/move-system-databases.md).  
  
### Database options for tempdb in SQL Server

The following table lists the default value for each database option in the `tempdb` database and whether the option can be modified. To view the current settings for these options, use the [sys.databases](../../relational-databases/system-catalog-views/sys-databases-transact-sql.md) catalog view.  
  
|Database option|Default value|Can be modified|  
|---------------------|-------------------|---------------------|  
|ALLOW_SNAPSHOT_ISOLATION|OFF|Yes|  
|ANSI_NULL_DEFAULT|OFF|Yes|  
|ANSI_NULLS|OFF|Yes|  
|ANSI_PADDING|OFF|Yes|  
|ANSI_WARNINGS|OFF|Yes|  
|ARITHABORT|OFF|Yes|  
|AUTO_CLOSE|OFF|No|  
|AUTO_CREATE_STATISTICS|ON|Yes|  
|AUTO_SHRINK|OFF|No|  
|AUTO_UPDATE_STATISTICS|ON|Yes|  
|AUTO_UPDATE_STATISTICS_ASYNC|OFF|Yes|  
|CHANGE_TRACKING|OFF|No|  
|CONCAT_NULL_YIELDS_NULL|OFF|Yes|  
|CURSOR_CLOSE_ON_COMMIT|OFF|Yes|  
|CURSOR_DEFAULT|GLOBAL|Yes|  
|Database Availability Options|ONLINE<br /><br /> MULTI_USER<br /><br /> READ_WRITE|No<br /><br /> No<br /><br /> No|  
|DATE_CORRELATION_OPTIMIZATION|OFF|Yes|  
|DB_CHAINING|ON|No|  
|ENCRYPTION|OFF|No|  
|MIXED_PAGE_ALLOCATION|OFF|No|  
|NUMERIC_ROUNDABORT|OFF|Yes|  
|PAGE_VERIFY|CHECKSUM for new installations of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]<br /><br /> NONE for upgrades of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]|Yes|  
|PARAMETERIZATION|SIMPLE|Yes|  
|QUOTED_IDENTIFIER|OFF|Yes|  
|READ_COMMITTED_SNAPSHOT|OFF|No|  
|RECOVERY|SIMPLE|No|  
|RECURSIVE_TRIGGERS|OFF|Yes|  
|Service Broker Options|ENABLE_BROKER|Yes|  
|TRUSTWORTHY|OFF|No|  
  
For a description of these database options, see [ALTER DATABASE SET Options (Transact-SQL)](../../t-sql/statements/alter-database-transact-sql-set-options.md).  
  
## tempdb database in SQL Database

### tempdb sizes for DTU-based service tiers

<!-- tempdb being larger for Basic and 50 eDTU pools than for 100-400 eDTU pools reflects actual config (historical reasons) --> 

|Service-level objective|Maximum `tempdb` data file size (GB)|Number of `tempdb` data files|Maximum `tempdb` data size (GB)|
|---|---:|---:|---:|
|Basic|13.9|1|13.9|
|S0|13.9|1|13.9|
|S1|13.9|1|13.9|
|S2|13.9|1|13.9|
|S3|32|1|32
|S4|32|2|64|
|S6|32|3|96|
|S7|32|6|192|
|S9|32|12|384|
|S12|32|12|384|
|P1|13.9|12|166.7|
|P2|13.9|12|166.7|
|P4|13.9|12|166.7|
|P6|13.9|12|166.7|
|P11|13.9|12|166.7|
|P15|13.9|12|166.7|
|Basic Elastic Pools (all DTU configurations)|13.9|12|166.7|
|Standard Elastic Pools (50 eDTU)|13.9|12|166.7|
|Standard Elastic Pools (100 eDTU)|32|1|32|
|Standard Elastic Pools (200 eDTU)|32|2|64|
|Standard Elastic Pools (300 eDTU)|32|3|96|
|Standard Elastic Pools (400 eDTU)|32|3|96|
|Standard Elastic Pools (800 eDTU)|32|6|192|
|Standard Elastic Pools (1200 eDTU)|32|10|320|
|Standard Elastic Pools (1600-3000 eDTU)|32|12|384|
|Premium Elastic Pools (all DTU configurations)|13.9|12|166.7|
||||

### tempdb sizes for vCore-based service tiers

See [vCore-based resource limits](/azure/sql-database/sql-database-vcore-resource-limits).

## Restrictions

The following operations can't be performed on the `tempdb` database:  
  
- Adding filegroups.
- Backing up or restoring the database.
- Changing collation. The default collation is the server collation.
- Changing the database owner. `tempdb` is owned by *sa*.
- Creating a database snapshot.
- Dropping the database.
- Dropping the *guest* user from the database.
- Enabling Change Data Capture.
- Participating in database mirroring.
- Removing the primary filegroup, primary data file, or log file.
- Renaming the database or primary filegroup.
- Running `DBCC CHECKALLOC`.
- Running `DBCC CHECKCATALOG`.
- Setting the database to `OFFLINE`.
- Setting the database or primary filegroup to `READ_ONLY`.
  
## Permissions

Any user can create temporary objects in `tempdb`. Users can access only their own objects, unless they receive additional permissions. It's possible to revoke the connect permission to `tempdb` to prevent a user from using `tempdb`. We don't recommend it because some routine operations require the use of `tempdb`.  

## Optimizing tempdb performance in SQL Server
The size and physical placement of the `tempdb` database can affect the performance of a system. For example, if the size that's defined for `tempdb` is too small, part of the system-processing load might be taken up with autogrowing `tempdb` to the size required to support the workload every time you restart the instance of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)].

If possible, use [instant file initialization](../../relational-databases/databases/database-instant-file-initialization.md) to improve the performance of growth operations for data files.

Preallocate space for all `tempdb` files by setting the file size to a value large enough to accommodate the typical workload in the environment. Preallocation prevents `tempdb` from expanding too often, which affects performance. The `tempdb` database should be set to autogrow to increase disk space for unplanned exceptions.

Data files should be of equal size within each [filegroup](../../relational-databases/databases/database-files-and-filegroups.md#filegroups), because [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] uses a proportional-fill algorithm that favors allocations in files with more free space. Dividing `tempdb` into multiple data files of equal size provides a high degree of parallel efficiency in operations that use `tempdb`.

Set the file growth increment to a reasonable size to prevent the `tempdb` database files from growing by too small a value. If the file growth is too small compared to the amount of data that's being written to `tempdb`, `tempdb` might have to constantly expand. That will affect performance.

To check current size and growth parameters for `tempdb`, use the following query:

```sql
 SELECT name AS FileName,
    size*1.0/128 AS FileSizeInMB,
    CASE max_size
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file grows to a maximum size of 2 TB.'
    END,
    growth AS 'GrowthValue',
    'GrowthIncrement' =
        CASE
            WHEN growth = 0 THEN 'Size is fixed.'
            WHEN growth > 0 AND is_percent_growth = 0
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files;
GO
```

Put the `tempdb` database on a fast I/O subsystem. Use disk striping if there are many directly attached disks. Individual or groups of `tempdb` data files don't necessarily need to be on different disks or spindles unless you're also encountering I/O bottlenecks.

Put the `tempdb` database on disks that differ from the disks that user databases use.

## Performance improvements in tempdb for SQL Server
Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)], `tempdb` performance is further optimized in the following ways:  
  
- Temporary tables and table variables are cached. Caching allows operations that drop and create the temporary objects to run very quickly. Caching also reduces page allocation and metadata contention.  
- The allocation page latching protocol is improved to reduce the number of `UP` (update) latches that are used.  
- Logging overhead for `tempdb` is reduced to reduce disk I/O bandwidth consumption on the `tempdb` log file.  
- Setup adds multiple `tempdb` data files during a new instance installation. You can accomplish this task by using the new UI input control in the **Database Engine Configuration** section and the command-line parameter `/SQLTEMPDBFILECOUNT`. By default, setup adds as many `tempdb` data files as the logical processor count or eight, whichever is lower.  
- When there are multiple `tempdb` data files, all files autogrow at the same time and by the same amount, depending on growth settings. [Trace flag 1117](../../t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql.md) is no longer required.  
- All allocations in `tempdb` use uniform extents. [Trace flag 1118](../../t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql.md) is no longer required.  
- For the primary filegroup, the `AUTOGROW_ALL_FILES` property is turned on and the property can't be modified.

For more information on performance improvements in `tempdb`, see the blog article [TEMPDB - Files and Trace Flags and Updates, Oh My!](/archive/blogs/sql_server_team/tempdb-files-and-trace-flags-and-updates-oh-my).

## Memory-optimized tempdb metadata
Metadata contention in `tempdb` has historically been a bottleneck to scalability for many workloads running on [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] introduces a new feature that's part of the [in-memory database](../in-memory-database.md) feature family: memory-optimized tempdb metadata. 

This feature effectively removes this bottleneck and unlocks a new level of scalability for tempdb-heavy workloads. In [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)], the system tables involved in managing temporary table metadata can be moved into latch-free, non-durable, memory-optimized tables.

Watch this seven-minute video for an overview of how and when to use memory-optimized tempdb metadata:

> [!VIDEO https://channel9.msdn.com/Shows/Data-Exposed/How-and-When-To-Memory-Optimized-TempDB-Metadata/player?WT.mc_id=dataexposed-c9-niner]


### Configuring and using memory-optimized tempdb metadata

To opt in to this new feature, use the following script:

```sql
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;
```

This configuration change requires a restart of the service to take effect.

You can verify whether or not `tempdb` is memory-optimized by using the following T-SQL command:

```sql
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');
```

If the server fails to start for any reason after you enable memory-optimized `tempdb` metadata, you can bypass the feature by starting the SQL Server instance with [minimal configuration](../../database-engine/configure-windows/start-sql-server-with-minimal-configuration.md) through the **-f** startup option. You can then disable the feature and restart SQL Server in normal mode.

To protect the server from potential out-of-memory conditions, you can bind `tempdb` to a [resource pool](../in-memory-oltp/bind-a-database-with-memory-optimized-tables-to-a-resource-pool.md). This is done through the [`ALTER SERVER`](../../t-sql/statements/alter-server-configuration-transact-sql.md) command rather than the steps you would normally follow to bind a resource pool to a database.

```sql
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON (RESOURCE_POOL = 'pool_name');
```

This change also requires a restart to take effect, even if memory-optimized tempdb metadata is already enabled.

### Memory-optimized tempdb limitations

- Toggling the feature on and off is not dynamic. Because of the intrinsic changes that need to be made to the structure of `tempdb`, a restart is required to either enable or disable the feature.

- A single transaction is not allowed to access memory-optimized tables in more than one database. Any transactions that involve a memory-optimized table in a user database won't be able to access `tempdb` system views in the same transaction. If you try to access `tempdb` system views in the same transaction as a memory-optimized table in a user database, you'll receive the following error:
    
  ```
  A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.
  ```
    
  Example:
    
  ```sql
  BEGIN TRAN;
  
  SELECT *
  FROM tempdb.sys.tables;  -----> Creates a user in-memory OLTP transaction in tempdb
  
  INSERT INTO <user database>.<schema>.<mem-optimized table>
  VALUES (1); ----> Tries to create a user in-memory OLTP transaction in the user database but will fail
  
  COMMIT TRAN;
  ```
    
- Queries against memory-optimized tables don't support locking and isolation hints, so queries against memory-optimized `tempdb` catalog views won't honor locking and isolation hints. As with other system catalog views in [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], all transactions against system views will be in `READ COMMITTED` (or in this case, `READ COMMITTED SNAPSHOT`) isolation.

- [Columnstore indexes](../indexes/columnstore-indexes-overview.md) can't be created on temporary tables when memory-optimized `tempdb` metadata is enabled.

- Due to the limitation on columnstore indexes, use of the `sp_estimate_data_compression_savings` system stored procedure with the `COLUMNSTORE` or `COLUMNSTORE_ARCHIVE` data compression parameter is not supported when memory-optimized `tempdb` metadata is enabled.

> [!NOTE] 
> These limitations apply only when you're referencing `tempdb` system views. You can create a temporary table in the same transaction as you access a memory-optimized table in a user database, if desired.

## Capacity planning for tempdb in SQL Server
Determining the appropriate size for `tempdb` in a [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] production environment depends on many factors. As described earlier, these factors include the existing workload and the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] features that are used. We recommend that you analyze the existing workload by performing the following tasks in a SQL Server test environment:

- Set autogrow on for `tempdb`.
- Run individual queries or workload trace files and monitor `tempdb` space use.
- Execute index maintenance operations such as rebuilding indexes, and monitor `tempdb` space.
- Use the space-use values from the previous steps to predict your total workload usage. Adjust this value for projected concurrent activity, and then set the size of `tempdb` accordingly.

## Monitoring tempdb use
Running out of disk space in `tempdb` can cause significant disruptions in the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] production environment. It can also prevent applications that are running from completing operations. You can use the [sys.dm_db_file_space_usage](../../relational-databases/system-dynamic-management-views/sys-dm-db-session-space-usage-transact-sql.md) dynamic management view to monitor the disk space that's used in the `tempdb` files:

```sql
 -- Determining the amount of free space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [free pages],
  (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

-- Determining the amount of space used by the version store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
  (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;

-- Determining the amount of space used by internal objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
  (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;

-- Determining the amount of space used by user objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
  (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM sys.dm_db_file_space_usage;
 ```

To monitor the page allocation or deallocation activity in `tempdb` at the session or task level, you can use the [sys.dm_db_session_space_usage](../../relational-databases/system-dynamic-management-views/sys-dm-db-session-space-usage-transact-sql.md) and [sys.dm_db_task_space_usage](../../relational-databases/system-dynamic-management-views/sys-dm-db-task-space-usage-transact-sql.md) dynamic management views. These views can help you identify large queries, temporary tables, or table variables that are using lots of `tempdb` disk space. You can also use several counters to monitor the free space that's available in `tempdb` and the resources that are using `tempdb`.

```sql
-- Obtaining the space consumed by internal objects in all currently running tasks in each session
SELECT session_id,
  SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
  SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;

-- Obtaining the space consumed by internal objects in the current session for both running and completed tasks
SELECT R2.session_id,
  R1.internal_objects_alloc_page_count
  + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count
  + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id
GROUP BY R2.session_id, R1.internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count;;
```

## Related content
[SORT_IN_TEMPDB option for indexes](../../relational-databases/indexes/sort-in-TempDB-option-for-indexes.md)    
[System databases](../../relational-databases/databases/system-databases.md)    
[sys.databases](../../relational-databases/system-catalog-views/sys-databases-transact-sql.md)    
[sys.master_files](../../relational-databases/system-catalog-views/sys-master-files-transact-sql.md)    
[Move database files](../../relational-databases/databases/move-database-files.md)