Enable and run tempdb on tmpfs for SQL Server 2025 Preview on Linux

Applies to: SQL Server 2025 (17.x) Preview - Linux

This article guides you to enable and run tempdb database files on the tmpfs filesystem in SQL Server 2025 (17.x) Preview.

SQL Server on Linux and containers traditionally support XFS and ext4 filesystems for deploying SQL Server database files and logs. However, for temporary databases like tempdb, which don't require data to be saved from one uptime period to another, using a tmpfs filesystem that utilizes memory (RAM) can enhance overall performance for tempdb-based workloads.

For more information about the tmpfs filesystem, see tmpfs - The Linux Kernel documentation.

Physical or virtual machine deployments

To enable tmpfs support for SQL Server on Linux on physical or virtual machines, you need to mount the tmpfs filesystem correctly, which requires sudo access. Once the mount points are set up, you can place the tempdb files on these mounts and start SQL Server with tempdb files mounted on the tmpfs filesystem.

Enable tmpfs for tempdb

1. Create the tempdb directory.

   Use the mkdir command to create a directory for the tempdb database. Ensure that it's owned by the mssql user and group to allow SQL Server access:

   mkdir /var/opt/mssql/tempdb
   sudo chown mssql. /var/opt/mssql/tempdb
   

2. Mount tmpfs filesystem.

   Use the following command to mount the tmpfs filesystem:

   sudo mount -t tmpfs -o size=4G tmpfs /var/opt/mssql/tempdb/
   

   The -t option specifies the type of filesystem, which in this case is tmpfs.

   The -o option allows you to specify mount options. Here, size=4G sets the maximum size of the tmpfs to 4 GB, meaning it can use up to 4 GB of RAM.

   This command mounts tmpfs to the target directory used by SQL Server tempdb database.

   Optionally, add it to fstab to maintain the mounts across restarts:

   echo "tmpfs /var/opt/mssql/tempdb tmpfs defaults,size=4G 0 0" | sudo tee -a /etc/fstab
   

3. Update tempdb file location.

   Ensure SQL Server is up and running. Connect to your SQL Server instance using SQL Server Management Studio (SSMS) and running the following T-SQL commands.

   Identify all the tempdb files using the following T-SQL script, then run the ALTER DATABASE command to update the tempdb file location:

   SELECT [name],
          physical_name
   FROM sys.master_files
   WHERE database_id = 2;
   

   Based on the number of files you see for tempdb, you create the ALTER DATABASE commands as follows:

   ALTER DATABASE tempdb
       MODIFY FILE (NAME = tempdev, FILENAME = '/var/opt/mssql/tempdb/tempdb.mdf');
   
   ALTER DATABASE tempdb
       MODIFY FILE (NAME = tempdev2, FILENAME = '/var/opt/mssql/tempdb/tempdb2.mdf');
   
   ALTER DATABASE tempdb
       MODIFY FILE (NAME = tempdev3, FILENAME = '/var/opt/mssql/tempdb/tempdb3.mdf');
   
   ALTER DATABASE tempdb
       MODIFY FILE (NAME = tempdev4, FILENAME = '/var/opt/mssql/tempdb/tempdb4.mdf');
   
   ALTER DATABASE tempdb
       MODIFY FILE (NAME = templog, FILENAME = '/var/opt/mssql/tempdb/templog.ldf');
   

4. Restart SQL Server to apply the changes.

   sudo systemctl restart mssql-server
   

5. Verify tempdb files location.

   Once SQL Server is up and running, verify that the tempdb data and log files are now located in the new directory, by connecting to SQL Server using a tool like SQL Server Management Studio (SSMS).

   SELECT [name],
          physical_name
   FROM sys.master_files
   WHERE database_id = 2;
   

You should now see all the tempdb database files on the new mount path that is /var/opt/mssql/tempdb.

Hot resizing of the tmpfs mount

You can also hot-resize the tmpfs mount in case the tempdb is full. To hot-resize, run the following command to resize the tmpfs mount to 6 GB:

mount -o remount,size=6G /var/opt/mssql/tempdb

Optionally, make the new size persist across restarts by modifying the fstab entry:

sudo sed -i 's|tmpfs /var/opt/mssql/tempdb tmpfs defaults,size=4G 0 0|tmpfs /var/opt/mssql/tempdb tmpfs defaults,size=6G 0 0|' /etc/fstab

Container deployments

For developer workloads, tmpfs can be used for user databases. tmpfs filesystems can significantly speed up test cases for user databases deployed in containers. Since tmpfs uses RAM instead of disk storage, it allows for rapid read/write operations. This configuration is useful in development and testing environments where quick iterations are needed.

However, tmpfs for user databases is not supported. You can provide feedback related to user databases on tmpfs on GitHub.

Host only tempdb databases on tmpfs filesystem

If you're deploying a SQL Server container and want to ensure that the container uses tmpfs for the tempdb, you can run the following command:

docker run \
  -e ACCEPT_EULA=Y \
  -e MSSQL_SA_PASSWORD = <password>\
  --tmpfs /var/opt/mssql/tempdb:uid=10001,gid=10001,size=4G \
  -p 5433:1433 \
  --name sql1 \
  -h sql1 \
  --d mcr.microsoft.com/mssql/server:2025-latest

The --tmpfs command sets the size to 4 GB and the uid (user ID) and gid (group ID) to 10001 to ensure that the required permissions are set correctly for the tempdb files to be created.

Once the container is up and running, connect to the SQL Server using SSMS and move the tempdb files to the new location /var/opt/mssql/tempdb with the following T-SQL commands:

SELECT [name],
       physical_name
FROM sys.master_files
WHERE database_id = 2;

Based on the number of files that you see, modify the following command. In this example, there are four tempdb files and one log file. Use the following commands to move these files to their new location:

ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = '/var/opt/mssql/tempdb/tempdb.mdf');
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev2, FILENAME = '/var/opt/mssql/tempdb/tempdb2.mdf');
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev3, FILENAME = '/var/opt/mssql/tempdb/tempdb3.mdf');
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev4, FILENAME = '/var/opt/mssql/tempdb/tempdb4.mdf');
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '/var/opt/mssql/tempdb/templog.ldf');
GO

After moving the files, stop and restart the container using the following commands

docker stop sql1
docker start sql1

Deploy containers with all data and log files on tmpfs

docker run -e ACCEPT_EULA=Y \
  -e MSSQL_SA_PASSWORD=<password> \
  --tmpfs /var/opt/mssql/data:uid=10001,gid=10001,size=4G \
  -p 5434:1433 \
  --name sql2 \
  -h sql2 \
  -d mcr.microsoft.com/mssql/server:2025-latest

Related content

• Installation guidance for SQL Server on Linux
• Deploy and connect to SQL Server Linux containers
• Configure SQL Server on Linux with the mssql-conf tool