Create Multiple Tempdbs based on Logical CPUs

1. 1. Use below script to file current tempdb files 

select DB_NAME(mf.database_id) database_name 

, mf.name logical_name, mf.file_id 

, CONVERT (DECIMAL (20,2) 

, (CONVERT(DECIMAL, size)/128)) as [file_size_MB] 

, CASE mf.is_percent_growth 

WHEN 1 THEN 'Yes' 

ELSE 'No' 

END AS [is_percent_growth] 

, CASE mf.is_percent_growth 

WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%' 

WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB' 

END AS [growth_in_increment_of] 

, CASE mf.is_percent_growth 

WHEN 1 THEN CONVERT(DECIMAL(20,2) 

,(((CONVERT(DECIMAL, size)*growth)/100)*8)/1024) 

WHEN 0 THEN CONVERT(DECIMAL(20,2) 

, (CONVERT(DECIMAL, growth)/128)) 

END AS [next_auto_growth_size_MB] 

, physical_name from sys.master_files mf 

where database_id =2 and type_desc= 'rows' 

2. 2. Use below script to find number of logical CPUS 

SELECT cpu_count AS logicalCPUs FROM sys.dm_os_sys_info 

3. 3. Use below Alter DB command to add more tempdb files 

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev2', 

FILENAME = N'F:\Data\tempdev2.ndf' , SIZE =8MB , FILEGROWTH = 64MB) --<<--Update the data file location/Size/AutoGrowth 

GO

Move Tempdb

-----------------Move tempdb from C drive to D drive local disk drive-----------
1. Use below script to find file structure  

Use master 

GO 

  

SELECT  

name AS [LogicalName] 

,physical_name AS [Location] 

,state_desc AS [Status] 

FROM sys.master_files 

WHERE database_id = DB_ID(N'tempdb'); 

GO 

2.  Use below scripts to change tempdb location, but don’t forget to restart  SQL Server Service to after this changes 

----- 

-- Need to restart server/ SQL Server Service after this change 

USE master; 

GO 

  

ALTER DATABASE tempdb  

MODIFY FILE (NAME = tempdev, FILENAME = 'D:\tempdb.mdf'); 

GO 

  

ALTER DATABASE tempdb  

MODIFY FILE (NAME = tempdev2, FILENAME = 'D:\tempdev2.ndf'); 

GO 

  

ALTER DATABASE tempdb  

MODIFY FILE (NAME = templog, FILENAME = 'D:\templog.ldf'); 

GO