A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database below:

tempdb Database - SQL Server

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric Preview

This article describes the tempdb system database, a global resource available to all users connected to a Database Engine instance in SQL Server, Azure SQL Database, or Azure SQL Managed Instance.

Overview

The tempdb system database is a global resource that holds:

Operations within tempdb are minimally logged. tempdb is re-created every time the Database Engine is started so that the system always starts with an empty tempdb database. Temporary stored procedures and local temporary tables are dropped automatically when the session that created them disconnects.

tempdb never has anything to be saved from one uptime period of the Database Engine 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 SQL Server.

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

All tempdb data files should always have the same initial size and growth parameters.

Number of tempdb data files

Depending on the version of the Database Engine, its configuration, and the workload, tempdb might require multiple data files to mitigate allocation contention.

The recommended total number of data files depends on the number of logical processors on the machine. As general guidance:

For more information, see Recommendations to reduce allocation contention in SQL Server tempdb database.

To check current size and growth parameters for tempdb, use the sys.database_files catalog view in tempdb.

Move the tempdb data and log files in SQL Server

To move the tempdb data and log files, see Move system databases.

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 catalog view.

Database option Default value Can be modified ACCELERATED_DATABASE_RECOVERY OFF Yes1 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 AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN) OFF No CHANGE_TRACKING OFF No COMPATIBILITY_LEVEL Depends on the Database Engine version.

For more information, see ALTER DATABASE (Transact-SQL) compatibility level.

Yes CONCAT_NULL_YIELDS_NULL OFF Yes CONTAINMENT NONE No CURSOR_CLOSE_ON_COMMIT OFF Yes CURSOR_DEFAULT GLOBAL Yes Database state ONLINE No Database update READ_WRITE No Database user access MULTI_USER No DATE_CORRELATION_OPTIMIZATION OFF Yes DB_CHAINING ON No DELAYED_DURABILITY DISABLED

Regardless of this option, delayed durability is always enabled on tempdb.

Yes ENCRYPTION OFF No MIXED_PAGE_ALLOCATION OFF No NUMERIC_ROUNDABORT OFF Yes PAGE_VERIFY CHECKSUM for new installations of SQL Server

Existing PAGE_VERIFY value might be retained when an instance of SQL Server is upgraded in place.

Yes PARAMETERIZATION SIMPLE Yes QUOTED_IDENTIFIER OFF Yes READ_COMMITTED_SNAPSHOT OFF No RECOVERY SIMPLE No RECURSIVE_TRIGGERS OFF Yes Service Broker ENABLE_BROKER Yes TARGET_RECOVERY_TIME 60 Yes TEMPORAL_HISTORY_RETENTION ON Yes TRUSTWORTHY OFF No

1 Setting ACCELERATED_DATABASE_RECOVERY to ON in tempdb is supported starting with SQL Server 2025 (17.x) Preview. In previous versions of SQL Server, modifying the ACCELERATED_DATABASE_RECOVERY option for the tempdb database is not allowed.

For a description of these database options, see ALTER DATABASE SET Options (Transact-SQL).

tempdb in Azure SQL Database

In Azure SQL Database, some aspects of tempdb behavior and configuration are different from SQL Server.

For single databases, each database on a logical server has its own tempdb. In an elastic pool, tempdb is a shared resource for all databases in the same pool but temporary objects created by one database are not visible to other databases in the same elastic pool.

Objects in tempdb, including catalog views and dynamic management views (DMVs), are accessible via a cross-database reference to the tempdb database. For example, you can query the sys.database_files view:

SELECT file_id,
       type_desc,
       name,
       size,
       max_size,
       growth
FROM tempdb.sys.database_files;

Global temporary tables in Azure SQL Database are database-scoped. For more information, see Database scoped global temporary tables in Azure SQL Database.

To learn more about tempdb sizes in Azure SQL Database, review:

tempdb in SQL Managed Instance

In Azure SQL Managed Instance, some aspects of tempdb behavior and default configuration are different from SQL Server.

You can configure the number of tempdb files, their growth increments, and their maximum size. For more information on configuring tempdb settings in Azure SQL Managed Instance, see Configure tempdb settings for Azure SQL Managed Instance.

Azure SQL Managed Instance supports temporary objects in the same way as SQL Server, where all global temporary tables and global temporary stored procedures are accessible by all user sessions within the same SQL managed instance.

To learn more about tempdb sizes in Azure SQL Managed Instance, review resource limits.

tempdb in SQL database in Fabric

To learn more about tempdb sizes in SQL database in Microsoft Fabric Preview, review the resource limits section in Feature comparison: Azure SQL Database and SQL database in Microsoft Fabric.

Similarly to Azure SQL Database, global temporary tables in SQL database in Microsoft Fabric Preview are database-scoped. For more information, see Database scoped global temporary tables in Azure SQL Database.

Restrictions

The following operations can't be performed on the tempdb database:

Permissions

Any user can create temporary objects in tempdb.

Users can access only their own non-temporary objects in tempdb, unless they receive additional permissions.

It's possible to revoke the CONNECT permission on tempdb to prevent a database user or role from using tempdb. This isn't recommended because many operations require the use of tempdb.

Optimize tempdb performance in SQL Server

The size and physical placement of tempdb files can affect performance. For example, if the initial size of tempdb is too small, time and resources might be taken up to autogrow tempdb to the size required to support the workload every time the Database Engine instance is restarted.

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

SELECT name AS file_name,
       type_desc AS file_type,
       size * 8.0 / 1024 AS size_mb,
       max_size * 8.0 / 1024 AS max_size_mb,
       CAST(IIF(max_size = 0, 0, 1) AS bit) AS is_autogrowth_enabled,
       CASE WHEN growth = 0 THEN growth
            WHEN growth > 0 AND is_percent_growth = 0 THEN growth * 8.0 / 1024
            WHEN growth > 0 AND is_percent_growth = 1 THEN growth
       END
       AS growth_increment_value,
       CASE WHEN growth = 0 THEN 'Autogrowth is disabled.'
            WHEN growth > 0 AND is_percent_growth = 0  THEN 'Megabytes'
            WHEN growth > 0 AND is_percent_growth = 1  THEN 'Percent'
       END
       AS growth_increment_value_unit
FROM tempdb.sys.database_files;

Put the tempdb database on a fast I/O subsystem. Individual data files or groups of tempdb data files don't necessarily need to be on different disks unless you're encountering disk-level I/O bottlenecks.

If there is I/O contention between tempdb and user databases, put tempdb files on disks that differ from the disks that user databases use.

Note

To improve performance, delayed durability is always enabled on tempdb even if the database option DELAYED_DURABILITY is set to DISABLED. Because tempdb is recreated at startup, it doesn't go through a recovery process and doesn't provide a durability guarantee.

Improvements in tempdb for SQL Server Introduced in SQL Server 2025 (17.x) Preview Introduced in SQL Server 2022 (16.x) Introduced in SQL Server 2019 (15.x) Introduced in SQL Server 2017 (14.x) Introduced in SQL Server 2016 (13.x)

Temporary object metadata contention has historically been a bottleneck to scalability for many SQL Server workloads. To address that, SQL Server 2019 (15.x) introduced a feature that's part of the in-memory database feature family: Memory-optimized TempDB metadata.

Enabling the Memory-optimized TempDB metadata feature removes this bottleneck for workloads previously limited by temporary object metadata contention inside tempdb. Starting with SQL Server 2019 (15.x), the system tables involved in managing temporary object metadata can become latch-free, non-durable, memory-optimized tables.

Tip

Because of current limitations, we recommend enabling Memory-optimized TempDB metadata only when object metadata contention occurs and significantly impacts your workloads.

The following diagnostic query returns one or more rows if temporary object metadata contention is occurring. Each row represents a system table, and returns the number of sessions contending for access to that table at the time when this diagnostic query is executed.

SELECT OBJECT_NAME(dpi.object_id, dpi.database_id) AS system_table_name,
       COUNT(DISTINCT(r.session_id)) AS session_count
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS prc
CROSS APPLY sys.dm_db_page_info(prc.db_id, prc.file_id, prc.page_id, 'LIMITED') AS dpi
WHERE dpi.database_id = 2
      AND dpi.object_id IN (3, 9, 34, 40, 41, 54, 55, 60, 74, 75)
      AND UPPER(r.wait_type) LIKE N'PAGELATCH[_]%'
GROUP BY dpi.object_id, dpi.database_id;

Watch this seven-minute video for an overview of how and when to use Memory-optimized TempDB metadata feature:

Note

Currently, the Memory-optimized TempDB metadata feature is not available in Azure SQL Database, SQL database in Microsoft Fabric Preview, and Azure SQL Managed Instance.

Configure and use Memory-optimized TempDB metadata

The following sections include steps to enable, configure, verify, and disable the Memory-optimized TempDB metadata feature.

Enable

To enable this feature, use the following script:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

For more information, see ALTER SERVER. 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:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

If the returned value is 1 and a restart has occurred after enabling the feature, then the feature is enabled.

If the server fails to start for any reason after you enable Memory-optimized TempDB metadata, you can bypass the feature by starting the Database Engine instance with minimal configuration using the -f startup option. You can then disable the feature and remove the -f option to restart the Database Engine in normal mode.

Bind to resource pool to limit memory usage

To protect the server from potential out-of-memory conditions, we recommend that you bind tempdb to a resource governor resource pool that limits the memory consumed by Memory-optimized TempDB metadata. The following sample script creates a resource pool and sets its maximum memory to 20%, enables resource governor, and binds tempdb to the resource pool.

This example uses 20% as the memory limit for demonstration purposes. The optimal value in your environment might be larger or smaller depending on your workload, and can change over time if the workload changes.

CREATE RESOURCE POOL tempdb_resource_pool
WITH (MAX_MEMORY_PERCENT = 20);

ALTER RESOURCE GOVERNOR RECONFIGURE;

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON 
(RESOURCE_POOL = 'tempdb_resource_pool');

This change also requires a service restart to take effect, even if Memory-optimized TempDB metadata is already enabled.

Verify resource pool binding and monitor memory usage

To verify that tempdb is bound to a resource pool and to monitor memory usage statistics for the pool, use the following query:

WITH resource_pool AS
(
SELECT p.pool_id,
       p.name,
       p.max_memory_percent,
       dp.max_memory_kb,
       dp.target_memory_kb,
       dp.used_memory_kb,
       dp.out_of_memory_count
FROM sys.resource_governor_resource_pools AS p
INNER JOIN sys.dm_resource_governor_resource_pools AS dp
ON p.pool_id = dp.pool_id
)
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS is_tempdb_memory_optimized_metadata_enabled,
       rp.name AS resource_pool_name,
       rp.max_memory_percent,
       rp.max_memory_kb,
       rp.target_memory_kb,
       rp.used_memory_kb,
       rp.out_of_memory_count
FROM sys.databases AS d
LEFT JOIN resource_pool AS rp
ON d.resource_pool_id = rp.pool_id
WHERE d.name = 'tempdb';
Remove resource pool binding

To remove the resource pool binding while keeping Memory-optimized TempDB metadata enabled, execute the following command and restart the service:

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;
Disable

To disable Memory-optimized TempDB metadata, execute the following command and restart the service:

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF;
Limitations of Memory-optimized TempDB metadata Capacity planning for tempdb in SQL Server

Determining the appropriate size for tempdb depends on many factors. These factors include the workload and the Database Engine features that are used.

We recommend that you analyze tempdb space consumption by performing the following tasks in a test environment where you can reproduce your typical workload:

Monitor tempdb use

Running out of disk space in tempdb can cause significant disruptions and application downtime. You can use the sys.dm_db_file_space_usage dynamic management view to monitor the space used in the tempdb files.

For example, the following example script finds:

SELECT SUM(unallocated_extent_page_count) * 8.0 / 1024 AS tempdb_free_data_space_mb,
       SUM(version_store_reserved_page_count) * 8.0 / 1024 AS tempdb_version_store_space_mb,
       SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS tempdb_internal_object_space_mb,
       SUM(user_object_reserved_page_count) * 8.0 / 1024 AS tempdb_user_object_space_mb
FROM tempdb.sys.dm_db_file_space_usage;

To monitor page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can help you identify queries, temporary tables, or table variables that are using large amounts of tempdb space.

For example, use the following example script to obtain the tempdb space allocated and deallocated 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;

Use the following example script to find the tempdb allocated and currently consumed space by internal and user objects for each session and request, for both running and completed tasks:

WITH tempdb_space_usage AS
(
SELECT session_id,
       request_id,
       user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
       user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
FROM sys.dm_db_task_space_usage
UNION ALL
SELECT session_id,
       NULL AS request_id,
       user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
       user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - user_objects_deferred_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
FROM sys.dm_db_session_space_usage
)
SELECT session_id,
       COALESCE(request_id, 0) AS request_id,
       SUM(tempdb_allocations_page_count * 8) AS tempdb_allocations_kb,
       SUM(IIF (tempdb_current_page_count >= 0, tempdb_current_page_count, 0) * 8) AS tempdb_current_kb
FROM tempdb_space_usage
GROUP BY session_id, COALESCE (request_id, 0)
ORDER BY session_id, request_id;

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4