Shrink the tempdb data files
List the tempdb files, then generate the DBCC SHRINKFILE commands that reduce them to 8 GB, with a warning about clearing the plan cache.
To shrink the tempdb files, first check their size and growth settings, then run DBCC SHRINKFILE on each file. The script below lists the files and generates the commands.
DBCC FREEPROCCACHEclears the plan cache of the whole instance, not only the cache of tempdb. The generated commands include it.
List the tempdb files
select name AS FileName,size*1.0/128 AS FileSizeinMB
, CASE max_size
WHEN 0 THEN 'Autogrowth is off.'
WHEN -1 THEN 'Autogrowth is on.'
ELSE 'Growth is limited to a maximum size.'
END AutogrowthStatus
, growth AS 'GrowthValue'
,CASE
WHEN growth = 0 THEN 'Size is fixed and will not grow.'
WHEN growth > 0
AND is_percent_growth = 0
THEN 'Growth value is in 8-KB pages.'
ELSE 'Growth value is a percentage.'
END
FROM tempdb.sys.database_files
The size is returned in MB (size is in 8-KB pages). GrowthValue is in 8-KB pages when the growth is not a percentage.
Generate the shrink commands
The cursor prints, for each tempdb file, a CHECKPOINT, a DBCC FREEPROCCACHE, and a DBCC SHRINKFILE that shrinks the file to 8192 MB (8 GB).
DECLARE @file varchar(1000)
DECLARE cur_file CURSOR
for select name from tempdb.sys.database_files
open cur_file
fetch cur_file into @file
while (@@FETCH_STATUS = 0)
begin
print 'CHECKPOINT'
print 'go'
print 'DBCC FREEPROCCACHE'
print 'go'
print 'DBCC SHRINKFILE (' + @file + ', 8192)'
print 'go'
fetch cur_file into @file
end
close cur_file
deallocate cur_file
Example of commands to run
CHECKPOINT
GO
DBCC FREEPROCCACHE
GO
DBCC SHRINKFILE (tempdev, 8192)
GO
CHECKPOINT
GO
DBCC FREEPROCCACHE
GO
DBCC SHRINKFILE (tempdev2, 8192)
GO
CHECKPOINT
GO
DBCC FREEPROCCACHE
GO
DBCC SHRINKFILE (tempdev3, 8192)
GO
CHECKPOINT
GO
DBCC FREEPROCCACHE
GO
DBCC SHRINKFILE (tempdev4, 8192)
GO