–Add TempDB Files to number of prcessors (up to 8)

DECLARE @dbname VARCHAR(200);
DECLARE @datafilename VARCHAR(200);
DECLARE @logfilename VARCHAR(200);
DECLARE @cpucount SMALLINT;
DECLARE @datafilepath VARCHAR(200);
DECLARE @logilepath VARCHAR(200);
DECLARE @datasizeMB INT;
DECLARE @logsizeMB INT;
DECLARE @datafilegrowthMB INT;
DECLARE @logfilegrowthMB INT;
DECLARE @sqlstring VARCHAR(8000);
DECLARE @loop SMALLINT;
SELECT @dbname = ‘tempdb’;
SELECT @datasizeMB = 1024;
SELECT @logsizeMB = 1024;
SELECT @datafilegrowthMB = 512;
SELECT @logfilegrowthMB = 512;
SELECT @cpucount = CASE
WHEN cpu_count < = 8 THEN cpu_count WHEN cpu_count > 8
THEN 8
END
FROM sys.dm_os_sys_info;
SELECT @loop = 2;
SELECT @datafilename = mf.name
FROM sys.master_files mf
INNER JOIN sys.databases d ON mf.database_id = d.database_id
WHERE d.name = @dbname
AND mf.file_id = 1;
SELECT @logfilename = mf.name
FROM sys.master_files mf
INNER JOIN sys.databases d ON mf.database_id = d.database_id
WHERE d.name = @dbname
AND mf.file_id = 2;
SELECT @datafilepath = SUBSTRING(mf.physical_name, 0, LEN(mf.physical_name)-CHARINDEX(‘\’, REVERSE(mf.physical_name), 0)+2)
FROM sys.master_files mf
INNER JOIN sys.databases d ON mf.database_id = d.database_id
WHERE d.name = @dbname
AND mf.file_id = 1;
SELECT @logilepath = SUBSTRING(mf.physical_name, 0, LEN(mf.physical_name)-CHARINDEX(‘\’, REVERSE(mf.physical_name), 0)+2)
FROM sys.master_files mf
INNER JOIN sys.databases d ON mf.database_id = d.database_id
WHERE d.name = @dbname
AND mf.file_id = 2;
SET @sqlstring = ‘
USE [master]
;
ALTER DATABASE [‘+@dbname+’] MODIFY FILE ( NAME = N”’+@datafilename+”’, SIZE = ‘+CAST(@datasizeMB AS VARCHAR(20)
)+’MB , FILEGROWTH = ‘+CAST(@datafilegrowthMB AS VARCHAR(20)
)+’MB)
;
ALTER DATABASE [‘+@dbname+’] MODIFY FILE ( NAME = N”’+@logfilename+”’, SIZE = ‘+CAST(@logsizeMB AS VARCHAR(20)
)+’MB , FILEGROWTH = ‘+CAST(@logfilegrowthMB AS VARCHAR(20)
)+’MB)
;
‘;
WHILE @loop <= @cpucount BEGIN SET @sqlstring = @sqlstring+'ALTER DATABASE ['+@dbname+'] ADD FILE ( NAME = N'''+@datafilename+CAST(@loop AS VARCHAR(2) )+''', FILENAME = N'''+@datafilepath+@datafilename+CAST(@loop AS VARCHAR(2) )+'.ndf'' , SIZE = '+CAST(@datasizeMB AS VARCHAR(20) )+'MB , FILEGROWTH = '+CAST(@datafilegrowthMB AS VARCHAR(20) )+'MB) ; '; SET @loop = @loop + 1; END; EXEC (@sqlstring );