SQL Server TempDB Tutorial

By: Eric Blinn   |   Updated: 2020-07-17   |   Comments (3)   |   Related: More > System Databases

Problem

SQL Server comes installed with four system databases by default. They are master, model, msdb, and TempDB. I need to know what the system database “TempDB” is and what it is used for. These previous tutorials cover master, model and msdb in great detail.

Solution

This tip will cover the TempDB database. TempDB is a database that has many functions within SQL Server, but it is rarely called explicitly. It has so many functions that it is often one of the busiest, if not the busiest database on most SQL Server instances. Read on to learn what many of these functions are!

SQL Server TempDB Overview

What is the purpose of SQL Server TempDB?

One of the functions of TempDB is to act something like a page or swap file would at the operating system level. If a SQL Server operation is too large to be completed in memory or if the initial memory grant for a query is too small, the operation can be moved to disk in TempDB.

Another function of TempDB is to store temporary tables. Anyone who has created a temporary table in T-SQL using a pound or hash prefix (#) or the double pound/hash prefix (##) has created an object in TempDB as this is where those are stored.

CREATE TABLE #MSSQLTips (RowID int);

When a trigger is executing the inserted and deleted virtual tables are stored in TempDB.

Any databases that use READ COMMITTED SNAPSHOT ISOLATION (RCSI) will have their row versioning information stored in TempDB.

Each of the above functions of TempDB are completed without ever actually mentioning TempDB by name and thus can be surprising uses for TempDB.

TempDB can also be called explicitly in a few ways. Tables can be generated in TempDB by referencing the database in a create statement. The code looks exactly like a normal DDL operation, but when run in TempDB the table is, by definition, a temporary table.

CREATE TABLE TempDB.dbo.MSSQLTips(RowID int);

TempDB is regenerated upon every start of the SQL Server instance. Any objects that may have been created in TempDB during a previous session will not persist upon a service restart. TempDB gets its initial object list from the model database which is generally going to be empty or nearly empty.

Index maintenance can optionally be asked to do sorts in TempDB rather than attempting to do so using free space in the user database.

ALTER INDEX [PK_numbers] ON [dbo].[numbers] REBUILD WITH (SORT_IN_TEMPDB = ON);

Can I run SQL Server without a TempDB database?

SQL Server cannot run (except in very extreme recovery scenarios and for very short periods) without a TempDB.

Is the version of the TempDB database unique to the version, edition, and patch level of SQL Server?

TempDB is a mostly blank database and there is nothing particularly unique about any edition or patch level of SQL Server.

There have been only 2 meaningful changes to TempDB in recent years at the version level. The first is that starting in SQL Server 2016 the behavior of TempDB was changed such that enabling trace flags T1117 and T1118 are no longer considered a best practice. The second is that starting in SQL Server 2019 some heavily used system objects in TempDB were upgraded to in-memory tables to reduce contention and improve overall server performance.

Can you drop or rename the TempDB database and should you do this for any reason?

TempDB cannot be dropped, detached, taken offline, or renamed. Attempting any of these operations will return an error. There is no reason that this should ever be attempted as this is a critical system database.

SQL Server TempDB Database Location

What physical files and names support the TempDB database?

The default logical filenames are tempdev for data and templog for log. They can be found on disk as tempdb.mdf and templog.ldf respectively. TempDB commonly has many data files.

How many TempDB data files should there be?

There should be one TempDB data file for each thread/core/vCPU on the instance with a maximum of 8.

Where do I find the TempDB database on disk and in SSMS?

The files can be found by querying sys.sysfiles dmv or the file pane on the database properties window.

SELECT * FROM TempDB.sys.sysfiles;

Does it make a difference where the TempDB database files reside (do they need to be on C:\, SAN, etc.)?

Best practices dictate that your TempDB data files should be on the fastest storage available due to their activity levels. For some instances this can mean storing them with the user database data files, but a separate volume on separate disk is preferred, if available.

There are differing opinions on the TempDB log file, but this author prefers putting this file with the other transaction log files.

Can the TempDB database be moved?

The TempDB database can be moved. The method for moving TempDB is easy to implement. Simply modify the FILENAME property of the desired file(s). No files need to be moved like when other system databases are moved. SQL Server will create the file(s) with the new name and/or in the new location at the next service start. Just don’t forget to go back and delete the now unused TempDB files that will be left behind.

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', FILENAME = N'G:\DATA\tmp.mdf');
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', FILENAME = N'H:\LOG\tmplog.ldf');
GO

SQL Server TempDB Database Size and Growth

How should the database and transaction log growth settings be configured for the TempDB database?

Autogrowth settings should be set to specific numbers of MB, not a percentage. Every data file should have the same number of MBs set for growth.

Right-sizing the files for a database is a generally a best practice, but never more so than for TempDB. Monitor the instance – especially if it is new – and determine if the files are growing during normal operation. If so, change the starting size such that SQL Server will not be forced to grow the files consistently after every service start.

What is the typical size of the TempDB database and how big can it get?

There is no good answer to this question. What is typical for one instance may not be typical for another. This author has seen instances where the TempDB is basically unused averaging only a few MB of used space at any given time. Yet, others are constantly being queried and using hundreds of GB of space – even into space measured in TB – especially during an index maintenance window.

When deciding how much space to dedicate to TempDB on a new instance there are a few things that can be done. Is the new instance replacing an older, existing instance? If so, use a perfmon trace or scheduled DMV capture to determine the used space in TempDB over the course of several days. Make sure to include a maintenance window in that capture. Try to allocate enough space on the new instance to account for the largest used space captured during the trace with additional wiggle room. Then right size the files to fit into that space allocation.

The 2 queries below can help. The first will take a snapshot of the size of the data and log files along with the space used within the files. This can be run on a regular basis to track usage over time. The second query will return the number of times the log file has grown since the last time the instance was restarted.

If there isn’t an existing workload that can be used as a guide and there isn’t other guidance, perhaps from a software vendor, then the only thing that can be done is to make sure there is space in TempDB to account for the largest anticipated individual objects that may be sorted in TempDB during an index maintenance windows. From there, monitor the usage and adjust accordingly.

USE TempDB;
GO
SELECT 
  GETUTCDATE() AS SnapshotDateTime
, groupid --0 = data, 1 = log
, SUM(size/128.) SizeOnDiskInMB
, SUM(FILEPROPERTY(name, 'spaceused')/128.) MBUsedWithinFile 
FROM TempDB.sys.sysfiles
GROUP BY groupid;
 
SELECT * 
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Log Growths'
  AND instance_name = 'tempdb';

Server TempDB Database Maintenance

Can you shrink the database and transaction log for the TempDB database?

Technically, yes, but this is not an operation that should ever be completed. The goal should always be to right-size TempDB. If some kind of one-off operation causes these files to grow inexplicably, they will return to normal upon the next service restart.

Should you rebuild indexes?

TempDB will not be a part of any index related maintenance plans. This means no rebuilding indexes, reorganizing, or updating of statistics.

Should you run integrity checks what is the code?

There are differing opinions on whether TempDB should be checked for integrity. This author will lay out the argument for and against and allow the reader to decide.

Against: An integrity check operation cannot check TempDB with the same totality as it can all other databases. The data that exists in TempDB is highly transient so the integrity check operation isn’t going to find much to check at any given time. For the same reason, any data that does become corrupt is likely to be active data that will cause an alert when being read from disk long before an integrity check operation would likely be called.

For: While an integrity check operation cannot check everything in TempDB that it checks in other databases that doesn’t mean it has no value. TempDB is an integral database and if there is corruption knowing about it is imperative so that corrective action can be taken.

The code to run an integrity check is below.

DBCC CHECKDB ('TempDB'); 

SQL Server TempDB Database Objects

What are the key tables and procedures in the TEMPDB database that are important for SQL Server Professionals to know about?

There aren’t any. This database is used for temporary storage of objects. It doesn’t come installed with any tables or procedures.

Can I store my own objects in the TEMPDB database?

Objects can certainly be created in TempDB, but they will not persist past the next start of the SQL Server service.

SQL Server TempDB Database Permissions

Who has access to the TempDB database and are there different levels of permissions?

Everyone has access and the same access to the TempDB.

SQL Server TempDB Database Backup

Do I need to backup the TempDB database?

TempDB cannot be backed up. It only stores transient data and is recreated every time the service starts so there is nothing to be recovered in the event of a disaster.

SQL Server TempDB Database Recovery Model

What recovery TempDB should the TempDB database have and can the recovery model be changed?

TempDB is in SIMPLE recovery and this cannot be changed.

SQL Server TempDB Database Restore

Is there ever a need to restore the TempDB database?

TempDB cannot be restored. There should be no data and no code in TempDB to be recovered. If TempDB becomes corrupt, simply delete the data and log files so that new ones can be generated upon the next service start up.

Next Steps

• This tip shows how to make alerts for problems found within TempDB sizes and disk space
• This tip shows how to enable instant file initialization, an important feature for all DBs, but especially TempDB if it isn’t properly sized.

About the author

Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips