Skip to content

tempdb-database.md

Latest commit

 

History

History
155 lines (110 loc) · 8.09 KB

tempdb-database.md

File metadata and controls

155 lines (110 loc) · 8.09 KB
title tempdb Database | Microsoft Docs
ms.custom
ms.date 03/04/2016
ms.prod sql-server-2016
ms.reviewer
ms.suite
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic article
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
caps.latest.revision 66
author BYHAM
ms.author rickbyh
manager jhubbard

tempdb Database

The tempdb system database is a global resource that is available to all users connected to the instance of [!INCLUDEssNoVersion] and is used to hold the following:

  • Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.

  • Internal objects that are created by the [!INCLUDEssDEnoversion], for example, work tables to store intermediate results for spools or sorting.

  • Row versions that are generated by data modification transactions in a database that uses read-committed using 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. This enables transactions to be rolled back. tempdb is re-created every time [!INCLUDEssNoVersion] 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. Therefore, there is never anything in tempdb to be saved from one session of [!INCLUDEssNoVersion] to another. Backup and restore operations are not allowed on tempdb.

Physical Properties of tempdb

The following table lists the initial configuration values of the tempdb data and log files. The sizes of these files may vary slightly for different editions of [!INCLUDEssNoVersion].

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 files depends on the number of (logical) cores on the machine. The value will be the number of cores or 8, whichever is lower.
The default value for the number of data files is based on the general guidelines in KB 2154845.

Performance Improvements in tempdb

In [!INCLUDEssNoVersion], tempdb performance is improved in the following ways:

  • Temporary tables and table variables may be cached. Caching allows operations that drop and create the temporary objects to execute very quickly and reduces page allocation contention.

  • Allocation page latching protocol is improved. This reduces the number of UP (update) latches that are used.

  • Logging overhead for tempdb is reduced. This reduces disk I/O bandwidth consumption on the tempdb log file.

  • Setup adds multiple tempdb data files during a new instance installation. This task can be accomplished with the new UI input control on the Database Engine Configuration section and a command line parameter /SQLTEMPDBFILECOUNT. By default, setup will add as many tempdb files as the CPU count or 8, whichever is lower.

  • When there are multiple tempdb data files, all files will autogrow at same time and by the same amount depending on growth settings. Trace flag 1117 is no longer required.

  • All allocations in tempdb use uniform extents. Trace flag 1118 is no longer required.

  • For the primary filegroup, the AUTOGROW_ALL_FILES property is turned on and the property cannot be modified.

Moving the tempdb Data and Log Files

To move the tempdb data and log files, see Move System Databases.

Database Options

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
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

MULTI_USER

READ_WRITE		 No

No

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 [!INCLUDEssNoVersion].

NONE for upgrades of [!INCLUDEssNoVersion].		 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).

Restrictions

The following operations cannot 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 only access their own objects, unless they receive additional permissions. It is possible to revoke the connect permission to tempdb to prevent a user from using tempdb, but this is not recommended as some routine operations require the use of tempdb.

Related Content

SORT_IN_TEMPDB Option For Indexes

System Databases

sys.databases (Transact-SQL)

sys.master_files (Transact-SQL)

Move Database Files

See Also

Working with tempdb in SQL Server 2005