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 SQL Server.

The size of tempdb can affect the performance of a system. For example, if the tempdb size is too small, the system processing could be too occupied with autogrowing the database to support your workload requirement every time that you start SQL Server. You can avoid this overhead by increasing the size of tempdb. For more information, see Optimizing tempdb Performance and Capacity Planning for tempdb.

In SQL Server, 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.
  
• The algorithm for allocating mixed pages in tempdb is improved.
  

Moving the tempdb Data and Log Files

Database Options

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 dbo.
  
• 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.
  

Concepts

Other Resources

Help and Information