Home > Workload Solutions > SQL Server > Guides > Microsoft SQL 2019 on Intel Optane Persistent Memory (PMem) Using Dell PowerEdge Servers > Move tempdb
By default, tempdb is created with one data file and one log file. It is on the operating system volume. If a significant workload is running on the SQL Server instance, tempdb can be a point of contention. For example, if there is an OLAP (read intensive) workload such as TPC-H running on the system, tempdb is heavily used for data sorting and storing intermediate results, and for other types of usage.
To change the data and log file location, use the following script:
ALTER DATABASE tempdb MODIFY FILE
(NAME = tempdev, FILENAME = '/<new_location>/tempdb01.mdf', SIZE = 16MB, FILEGROWTH = 64MB)
GO
ALTER DATABASE tempdb MODIFY FILE
(NAME = templog, FILENAME = '/<new_location>/templog.ldf', SIZE = 512MB, FILEGROWTH = 64MB)
GO
For our solution deployment, we used seven additional datafiles to the tempdb.
To add additional datafiles, use following sample script:
Note:
• Ensure that you change the location, size, and growth values before running this script.
• Give the mssql user a sufficient level of permission at the location where you are moving the tempdb files.
ALTER DATABASE tempdb ADD FILE
(NAME = tempdev2, FILENAME = '/<new_location>/tempdb02.mdf', SIZE = 16MB, FILEGROWTH = 64MB)
GO
ALTER DATABASE tempdb ADD FILE
(NAME = tempdev3, FILENAME = '/<new_location>/tempdb03.mdf', SIZE = 16MB, FILEGROWTH = 64MB)
GO
ALTER DATABASE tempdb ADD FILE
(NAME = tempdev4, FILENAME = '/<new_location>/tempdb04.mdf', SIZE = 16MB, FILEGROWTH = 64MB)
GO
ALTER DATABASE tempdb ADD FILE
(NAME = tempdev5, FILENAME = '/<new_location>/tempdb05.mdf', SIZE = 16MB, FILEGROWTH = 64MB)
GO
ALTER DATABASE tempdb ADD FILE
(NAME = tempdev6, FILENAME = '/<new_location>/tempdb06.mdf', SIZE = 16MB, FILEGROWTH = 64MB)
GO
ALTER DATABASE tempdb ADD FILE
(NAME = tempdev7, FILENAME = '/<new_location>/tempdb07.mdf', SIZE = 16MB, FILEGROWTH = 64MB)
GO
ALTER DATABASE tempdb ADD FILE
(NAME = tempdev8, FILENAME = '/<new_location>/tempdb08.mdf', SIZE = 16MB, FILEGROWTH = 64MB)
GO
When the script finishes, restart the SQL Server instance so that the changes take effect.