info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



T-SQL - Dynamically Re-Size TempDB Files
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2018-12-03








T-SQL:


--Target size for the tempdb data file:
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = target_size_in_MB)

--Target size for the tempdb log file:
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE = target_size_in_MB)

--Check current tempdb data file(s) size before change:
Select name, physical_name, type_desc, size/128 SizeMB from sys.master_files where type_desc ='ROWS' and database_id = 2

--Dynamically generate re-size commands for tempdb data files:
Select 'ALTER DATABASE tempdb MODIFY FILE (NAME =' + '''' + name + '''' + ', Size = 1024)' from sys.master_files where type_desc ='ROWS' and database_id = 2

--Example:
ALTER DATABASE tempdb MODIFY FILE (NAME ='tempdev', Size = 1024)
ALTER DATABASE tempdb MODIFY FILE (NAME ='tempdb2', Size = 1024)
ALTER DATABASE tempdb MODIFY FILE (NAME ='tempdb3', Size = 1024)
ALTER DATABASE tempdb MODIFY FILE (NAME ='tempdb4', Size = 1024)
ALTER DATABASE tempdb MODIFY FILE (NAME ='tempdb5', Size = 1024)
ALTER DATABASE tempdb MODIFY FILE (NAME ='tempdb6', Size = 1024)
ALTER DATABASE tempdb MODIFY FILE (NAME ='tempdb7', Size = 1024)
ALTER DATABASE tempdb MODIFY FILE (NAME ='tempdb8', Size = 1024)

--Check current tempdb data file(s) size after change:
Select name, physical_name, type_desc, size/128 SizeMB from sys.master_files where type_desc ='ROWS' and database_id = 2

--Check current tempdb log file size before change:
Select name, physical_name, type_desc, size/128 SizeMB from sys.master_files where type_desc ='LOG' and database_id = 2

--Dynamically generate re-size commands for tempdb log file:
Select 'ALTER DATABASE tempdb MODIFY FILE (NAME =' + '''' + name + '''' + ', Size = 1024)' from sys.master_files where type_desc ='LOG' and database_id = 2

--Example:
ALTER DATABASE tempdb MODIFY FILE (NAME ='templog', Size = 1024)

--Check current tempdb log file size after change:
Select name, physical_name, type_desc, size/128 SizeMB from sys.master_files where type_desc ='LOG' and database_id = 2