AddTempdb.sql

USE [master]
GO
-- This script will create upto 8 Tempdb files at the location specified and of the size specified.
--- Add files
Declare @data_file_path sysname = '/var/opt/mssql/data'
Declare @log_file_path sysname = '/var/opt/mssql/log'
Declare @sqlstr nvarchar(max) 
Declare @logicalfilename sysname
Declare @i int=1
Declare @data_file_size int=500
Declare @log_file_size int =100
Declare @num_files smallint

-- Set Temppdb files to nume CPU's with a cap at 8
declare @cpu_count int
select @cpu_count=cpu_count from  sys.dm_os_sys_info
if @cpu_count > 8
	set @num_files = 7
else
	set @num_files = @cpu_count-1

-- Modify existing files to the same size
-- For now default data location
set @sqlstr='ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ''' + @data_file_path + '/tempdb.mdf'',  SIZE = ' + cast(@data_file_size as varchar(20)) + 'MB , FILEGROWTH = 500MB);'
--print @sqlstr
exec (@sqlstr)
set @sqlstr='ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = ''' + @log_file_path + '/templog.ldf'',  SIZE = ' + cast(@log_file_size as varchar(20)) + 'MB , FILEGROWTH = 500MB);'
--print @sqlstr
exec (@sqlstr)

While @i <= @num_files
BEGIN
SET @logicalfilename = 'tempdev' + cast(@i as nvarchar(10)) 
SET @sqlstr = N'ALTER DATABASE [tempdb] ADD FILE ( NAME =N'''
SET @sqlstr = @sqlstr + @logicalfilename + ''', FILENAME = N''' + @data_file_path + '/' + @logicalfilename + '.ndf'', SIZE ='+ cast(@data_file_size as varchar(20)) + 'MB , FILEGROWTH = 500MB);'
print @sqlstr
exec (@sqlstr)
set @i = @i + 1
END
GO