Sets up tempdb: one data file per logical CPU (up to 8), all the same size and growth, plus the log. Missing files are added next to the first data file; existing files are never shrunk. Trace flags 1117 and 1118 are not needed for this on SQL Server 2016 and later. Tested on SQL Server 2017 and 2025 and safe to run more than once.

-- 04.Setup_TempDB_Files.sql
-- Author: Clive Roberts DBA. For SQL Server 2016 to 2022 (tested on SQL Server 2017 and 2025). Safe to run again.
-- Change the settings below if needed, then run it (F5, normal mode, not SQLCMD).
IF OBJECT_ID('tempdb..#InstallSettings') IS NOT NULL DROP TABLE #InstallSettings;
CREATE TABLE #InstallSettings (name sysname PRIMARY KEY, value nvarchar(400) NOT NULL);
INSERT INTO #InstallSettings (name, value) VALUES
    (N'TempDbFileSizeMB',      N'1024'),                              -- size of each tempdb data file and of the log
    (N'TempDbFileGrowthMB',    N'512');                               -- growth of each tempdb file
GO
USE [master];
-- One data file per logical CPU (at most 8), every data file and the log at least TempDbFileSizeMB,
-- all growing by TempDbFileGrowthMB. Adds missing files next to the first data file. Never shrinks a file.
DECLARE @size int   = (SELECT CONVERT(int, value) FROM #InstallSettings WHERE name = N'TempDbFileSizeMB');
DECLARE @growth int = (SELECT CONVERT(int, value) FROM #InstallSettings WHERE name = N'TempDbFileGrowthMB');
DECLARE @cpus int   = (SELECT cpu_count FROM sys.dm_os_sys_info);
DECLARE @want int   = CASE WHEN @cpus > 8 THEN 8 ELSE @cpus END;
DECLARE @have int   = (SELECT COUNT(*) FROM sys.master_files WHERE database_id = 2 AND type = 0);
DECLARE @first nvarchar(260) = (SELECT TOP (1) physical_name FROM sys.master_files WHERE database_id = 2 AND type = 0 ORDER BY file_id);
DECLARE @sep nchar(1) = CASE WHEN CHARINDEX(N'\', @first) > 0 THEN N'\' ELSE N'/' END;
DECLARE @folder nvarchar(260) = LEFT(@first, LEN(@first) - CHARINDEX(@sep, REVERSE(@first)) + 1);
DECLARE @name sysname, @cur int, @sql nvarchar(max), @n int = 2, @file nvarchar(400);

-- existing files: grow to the size (if smaller) and set the growth
DECLARE f CURSOR LOCAL FAST_FORWARD FOR SELECT name, size / 128 FROM sys.master_files WHERE database_id = 2;
OPEN f; FETCH NEXT FROM f INTO @name, @cur;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'ALTER DATABASE [tempdb] MODIFY FILE (NAME = ' + QUOTENAME(@name)
             + CASE WHEN @cur < @size THEN N', SIZE = ' + CONVERT(nvarchar(20), @size) + N'MB' ELSE N'' END
             + N', FILEGROWTH = ' + CONVERT(nvarchar(20), @growth) + N'MB);';
    EXEC (@sql);
    FETCH NEXT FROM f INTO @name, @cur;
END
CLOSE f; DEALLOCATE f;

-- add data files until there is one per CPU (max 8)
WHILE @have < @want
BEGIN
    WHILE EXISTS (SELECT 1 FROM sys.master_files WHERE database_id = 2
                  AND (name = N'temp' + CONVERT(nvarchar(3), @n)
                       OR physical_name = @folder + N'tempdb_mssql_' + CONVERT(nvarchar(3), @n) + N'.ndf'))
        SET @n += 1;
    SET @file = @folder + N'tempdb_mssql_' + CONVERT(nvarchar(3), @n) + N'.ndf';
    SET @sql = N'ALTER DATABASE [tempdb] ADD FILE (NAME = ' + QUOTENAME(N'temp' + CONVERT(nvarchar(3), @n))
             + N', FILENAME = ' + QUOTENAME(@file, N'''')
             + N', SIZE = ' + CONVERT(nvarchar(20), @size) + N'MB, FILEGROWTH = ' + CONVERT(nvarchar(20), @growth) + N'MB);';
    EXEC (@sql);
    SET @have += 1; SET @n += 1;
END

SELECT @have = COUNT(*) FROM sys.master_files WHERE database_id = 2 AND type = 0;
PRINT N'  tempdb: ' + CONVERT(nvarchar(3), @have) + N' data file(s) for ' + CONVERT(nvarchar(3), @cpus)
    + N' CPU(s), each at least ' + CONVERT(nvarchar(20), @size) + N' MB, growing ' + CONVERT(nvarchar(20), @growth) + N' MB';
IF (SELECT COUNT(DISTINCT size) FROM sys.master_files WHERE database_id = 2 AND type = 0) > 1
    PRINT N'  Note: the tempdb data files are not all the same size (larger ones are never shrunk) - SQL Server fills them most evenly when they match.';
GO