SQL Server: add data files to tempdb and move them
Generate the statements that add data files to tempdb, and the statements that move its files to another folder.
Two scripts to configure tempdb. The first adds data files until their number equals the number of logical processors; it only prints the ALTER DATABASE statements (the execution line is commented out). The second generates the statements that move the tempdb files to another folder.
Add data files
The new files are created in the same folder as the existing tempdev file.
USE [master]
GO
DECLARE @cpu_count int, @file_count int, @logical_name sysname, @file_name nvarchar(520), @physical_name nvarchar(520), @alter_command nvarchar(max)
SELECT
@physical_name = physical_name
FROM tempdb.sys.database_files
WHERE
name = 'tempdev'
SELECT
@file_count = COUNT(*)
FROM
tempdb.sys.
database_files
WHERE
type_desc = 'ROWS'
SELECT
@cpu_count = cpu_count
FROM
sys.dm_os_sys_info
WHILE @file_count < @cpu_count
BEGIN
SELECT @logical_name = 'tempdev' + CAST(@file_count AS nvarchar)
SELECT @file_name = REPLACE(@physical_name, 'tempdb.mdf', @logical_name + '.ndf')
SELECT @alter_command = 'ALTER DATABASE [tempdb] ADD FILE ( NAME =N''' + @logical_name + ''', FILENAME =N''' + @file_name + ''', SIZE = 100MB, FILEGROWTH = 50MB )'
PRINT @alter_command
--EXEC sp_executesql @alter_command
SELECT @file_count = @file_count + 1
END
Move the tempdb files
This query changes nothing: it generates one ALTER DATABASE tempdb MODIFY FILE statement per file, pointing to D:\SQLData\.
SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'
+ ' FILENAME = ''D:\SQLData\' + f.name
+ CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END
+ ''');'
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb');
Notes
- The target folder must exist, and the SQL Server service account needs write permission on it.
- The move takes effect at the next restart of the instance.
- The move script gives the
.mdfextension to every data file; adapt it if you want to distinguish primary and secondary files.