Creating tempdb Files

Scenarios

The tempdb system database is a global resource that is available to all users connected to an instance of SQL Server or SQL Database. It is a temporary database that cannot store data permanently. It is used to process intermediate data for various requests in the instance. Physical properties of tempdb in SQL Server are classified into the primary data files (.mdf), secondary data files (.ndf), and log files (.ldf). tempdb is re-created every time SQL Server is started.

There may be some issues or even service interruption if applications frequently create and drop tempdb files, especially in high-concurrency scenarios.

Microsoft recommends that the tempdb files be divided into multiple files. Generally, the number of files depends on the number of vCPUs (logical). If the number of vCPUs is greater than eight, use eight data files and then if contention continues, increase the number of data files by multiples of 4 until the contention is reduced to acceptable levels or make changes to the workload/code.

For more information, see tempdb Database in the Microsoft official website.

Constraints

By default, each RDS for SQL Server instance running SQL Server 2008, 2012, or 2014 Edition has one tempdb file, each instance running SQL Server 2016 Edition has four tempdb files, and each instance running SQL Server 2017 Edition has eight tempdb files.
Each RDS for SQL Server instance has only one log file no matter which SQL Server Edition they run.

Application Scenario

You need to determine the number of tempdb files to be created based on the instance specifications and scenarios. The following uses an example to show how to create 8 tempdb files for a SQL Server 2014 Enterprise Edition instance with 32 vCPUs.

Prerequisites

Visit the Microsoft website and obtain the installation package of SQL Server Management Studio. Double-click the installation package and complete the installation as instructed.
You have created an instance with 32 vCPUs running Microsoft SQL Server 2014 Enterprise Edition. For details, see Buy a DB Instance

Procedure

Start SQL Server Management Studio.

Choose Connect > Database Engine. In the displayed dialog box, enter login information.

Figure 1 Connecting to the server

**Table 1** Parameter description
Parameter	Description
Server name	Indicates the IP address and database port of the DB instance. Use a comma (,) to separate them. For example: x.x.x.x,8080. The IP address is the EIP that has been bound to the DB instance. The database port is that displayed on the Connectivity & Security page.
Authentication	Indicates the authentication mode. Select SQL Server Authentication.
Login	Indicates the database account used for accessing the instance. The default administrator account is rdsuser.
Password	Indicates the password of the database account.

View the tempdb information.
- Choose Databases > System Databases> tempdb. Right-click tempdb and choose Database Properties. In the displayed dialog box, view the current tempdb information.
  Figure 2 Viewing current tempdb information
- You can also run the following SQL statements to query the tempdb information:
  SELECT name AS FileName,
  
  size*1.0/128 AS FileSizeInMB,
  
  CASE max_size
  
  WHEN 0 THEN 'Autogrowth is off.'
  
  WHEN -1 THEN 'Autogrowth is on.'
  
  ELSE 'Log file grows to a maximum size of 2 TB.'
  
  END,
  
  growth AS 'GrowthValue',
  
  'GrowthIncrement' =
  
  CASE
  
  WHEN growth = 0 THEN 'Size is fixed.'
  
  WHEN growth > 0 AND is_percent_growth = 0
  
  THEN 'Growth value is in 8-KB pages.'
  
  ELSE 'Growth value is a percentage.'
  
  END
  
  FROM tempdb.sys.database_files;
  
  GO
Run the following statements to query the tempdb file name of the current DB instance:

SELECT name, physical_name

FROM sys.master_files

WHERE database_id = DB_ID('tempdb');

Figure 3 Viewing tempdb file names
On the Files tab in 3, view the paths of tempdb files.

Figure 4 Viewing tempdb paths
Run the following statements to migrate the tempdb files to D:\RDSDBDATA\DATA and specify the initial size and growth as required.

USE master;

GO

ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'D:\RDSDBDATA\DATA\tempdb.mdf', SIZE = 8MB, FILEGROWTH = 64MB);

GO

ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'D:\RDSDBDATA\DATA\templog.ldf', SIZE = 8MB, FILEGROWTH = 64MB);

GO

Figure 5 Moving tempdb files
On the Instances page of the RDS console, locate the target DB instance and choose More > Reboot in the Operation column to reboot the DB instance.

You can also click the target DB instance. On the displayed page, click Reboot in the upper right corner of the page.
Run the following SQL statements to check whether the tempdb files are successfully migrated:

SELECT name, physical_name

FROM sys.master_files

WHERE database_id = DB_ID('tempdb');

Figure 6 Viewing the migration results
Configure the file name, initial size, and growth as required. Add tempdb files using either of the following methods:
- Adding tempdb files through SQL statements
  Based on the number of vCPUs and tempdb files to be added, set the initial size to 8 MB and file growth to 64 MB.
  
  USE [master]
  
  GO
  
  ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp2', FILENAME = N'D:\RDSDBDATA\DATA\tempdb2.ndf', SIZE = 8MB, FILEGROWTH = 64MB)
  
  GO
  
  ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp3', FILENAME = N'D:\RDSDBDATA\DATA\tempdb3.ndf', SIZE = 8MB, FILEGROWTH = 64MB)
  
  GO
  
  ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp4', FILENAME = N'D:\RDSDBDATA\DATA\tempdb4.ndf', SIZE = 8MB, FILEGROWTH = 64MB)
  
  GO
  
  ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp5', FILENAME = N'D:\RDSDBDATA\DATA\tempdb5.ndf', SIZE = 8MB, FILEGROWTH = 64MB)
  
  GO
  
  ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp6', FILENAME = N'D:\RDSDBDATA\DATA\tempdb6.ndf', SIZE = 8MB, FILEGROWTH = 64MB)
  
  GO
  
  ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp7', FILENAME = N'D:\RDSDBDATA\DATA\tempdb7.ndf', SIZE = 8MB, FILEGROWTH = 64MB)
  
  GO
  
  ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp8', FILENAME = N'D:\RDSDBDATA\DATA\tempdb8.ndf', SIZE = 8MB, FILEGROWTH = 64MB)
  
  GO
- Adding tempdb files through SQL Server Management Studio On the Files tab in 3, click Add.
  Figure 7 Adding tempdb files through the client
After the configuration is complete, reboot the DB instance again by referring to 7.
Repeat 8 to check whether the tempdb files are successfully added.

Figure 8 Viewing the added tempdb files