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
|
|
|
|
|
|