LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon