Vitor Montalvão

Configuration, operations and restrictions of the tempdb SQL Server system database

June 28, 2017 by

Introduction

tempdb is one of the 4 system databases that exists in all SQL Server instances. The other databases are master, model and msdb. In case of using Replication, a fifth system database named distribution will also exist. You can find all existing system databases in SQL Server Management Studio (SSMS) under the Databases / System Databases folder:

tempdb usage

SQL Server uses tempdb database to store user objects, internal objects and version stores.

Version stores

Version stores are used to store row versions generated from operations as online reindex, triggers or snapshot isolation.

Internal objects

As the name indicates, internal objects are created internally by the SQL Server engine and are stored in tempdb database. These internal objects are:

  • Intermediate results for sorting process;
  • Intermediate results for hash joins and aggregates;
  • Intermediate results from queries that need to spool;
  • XML variables and large object (LOB) variables;
  • Keys from keyset cursors;
  • Query results from static cursors;
  • Messages in transit from Service Brokers;
  • Data for internal processing from INSTEAD OF triggers;
  • Internal use from DBCC CHECK command;
  • Database mail.

User objects

User temporary objects can be tables, stored procedures, table variables, the value returned from a table valued function or the mapping index for online clustered index build with the SORT_IN_TEMPDB option.

User objects can be defined as local temporary object by using a ‘#’ as prefix in the object name (e.g. #MyTempTable) or can be defined as global temporary objects by using the prefix ‘##’ before the object name (e.g. ##MyTempTable).

A local temporary object is destroyed when the scope where it was created, expires or terminates. The scope can be a stored procedure or a session.

A global temporary object is only destroyed when all sessions that are using it expires or terminates.

Configuration

A tempdb database was deemed to be so important that in SQL Server 2016 it has his own configuration setup screen during the SQL Server engine installation:

As can be seen, the number of files is filled automatically with the number of logical processors and it is requiring own location for the data and log files. I will next describe these configuration settings.

Data files

It is recommended to create a data file per logical processor for the tempdb database, until a maximum of 8 data files, meaning that if your server has more than 8 processors, you should not create more than 8 data files. This is only a best practice recommendation, though, and can be tweaked if necessary. Keep in mind that too many files may increase the cost of file switching, meaning increase the overhead and that is why recommendation is not to start with more than 8 data files.

Each of the multiple data files should have the same size so the SQL Server engine can apply the proportional fill optimization procedure to reduce UP latch contention. This procedure is a mechanism that will guarantee that each data file is filled in proportion to the free space that is available in the file so that all of the files fill up at about the same time.

Microsoft recommends to set an auto grow value for tempdb, but based on my own experience I will not recommend it, myself. My personal recommendation is to initially set the maximum size possible for the tempdb data files (divide the disk size by the number of data files) and immediately fill the disk so you can disable the auto grow to avoid any negative impact during the file grow process.

Shrinking files is also not a recommended operation for a tempdb database since it might change database files sizes and impact negatively on the proportional fill optimization procedure because this mechanism needs to have the same size for all data files.

Disk space

tempdb is one particular database that is more difficult to estimate the needed disk space. As showed in the tempdb usage section, it stores many kinds of objects and they are not so easy to estimate. Unfortunately, during all these years working with SQL Server I couldn’t yet find the magic formula but I would recommend to provide as much disk space you can with the minimum disk size being the same size as the largest table in the SQL Server instance. It is also wise to add some safety factor to prevent the database growth.

Fortunately, for the tempdb transaction log file, it is easier to estimate the necessary size. The tempdb, being a database with the recovery model set to Simple, it will be minimally logged. You can use a rule of thumb to create the transaction log file based on 20%-30% of the data size and tweak it later if needed.

The space in tempdb is vital for the health of the SQL Server instance. When running out of space the SQL Server instance may become unresponsive so it is always better to reserve more space than give less for the tempdb data and log files.

It is also good to have dedicated disks for a tempdb database to avoid a situation where other database files to consume space that might be needed later for tempdb. SSD is supported since SQL Server 2012 and can be used to store database files and because of the particularities of tempdb, as such, it is, in my opinion, the best database candidate to be stored in a SSD drive to achieve better performance results.

Operations

Database creation

tempdb is created every time the SQL Server instance starts, meaning that any existing object will be lost after the database recreation.

Permissions

By default, all users that have access to the SQL Server instance can create objects and perform queries in tempdb database, although the connect permission can be revoked from an user as it is for a regular database.

Move file locations

As in any regular database, tempdb data and log files can be moved to another location if and when needed to. The following is an example for moving 2 data files and 1 transaction log file to a new location in disk T:

Since tempdb is recreated every time the SQL Server instance restarts, there is no need to copy the current files to the new location. The only thing that it needs to be done to have these changes implemented is to perform a SQL Server service restart and then the new files will be created in the new location (folder structure should exist). After that, old tempdb data and log files can be deleted from old location.

Restrictions

tempdb has many restrictions and some are explained below.

Backup

tempdb is the only database that cannot be backed up, meaning of course, that it cannot be restored as well.

In SSMS you will not find the context menu for Backup and Restore on tempdb and if you try to execute the backup command you will receive the error ‘Backup and restore operations are not allowed on database tempdb’:

Recovery model

tempdb recovery model is set to Simple and cannot be changed. If you try to change it, you will receive the error ‘Option ‘RECOVERY’ cannot be set in database ‘tempdb’’:

Drop database

tempdb cannot be deleted. If you try to delete it, you will receive the error ‘Cannot drop the database ‘tempdb’ because it is a system database’:

Set offline

tempdb cannot be set to offline. If you try to do it you will receive the error ‘Option ‘OFFLINE’ cannot be set in database ‘tempdb’’:

DBCC CHECKCATALOG and CHECKALLOC

A DBCC CHECKALLOC or DBCC CHECKCATALOG cannot be run on a tempdb database:

Other restrictions

There are some more restrictions that are good to be known:

  • The tempdb database cannot be renamed;
  • The tempdb database owner is dbo and cannot be changed;
  • The tempdb database and its primary filegroup cannot be set to READ_ONLY status;
  • The tempdb does not allow for adding more filegroups for the database nor rename the primary filegroup;
  • The primary filegroup, primary data file, primary log file and the guest user of tempdb cannot be deleted;
  • The default collation for the tempdb is the SQL Server instance collation and cannot be changed;
  • Change Data Capture (CDC) cannot be enabled for tempdb;
  • The tempdb database cannot be part of a database mirroring solution.

Next articles in this series:

References


Vitor Montalvão
Latest posts by Vitor Montalvão (see all)

Related posts:

  1. SQL Server system databases – the model database
  2. The tempdb database, introduction and recommendations
  3. How to monitor the SQL Server tempdb database
  4. How to detect and prevent unexpected growth of the TempDB database
  5. Overview of the Shrink TempDB database in SQL Server
168 Views