LLI Wiki — My Digital Knowledge Base

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 FREEPROCCACHE clears 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
Mastodon LLIMastodon SocialFosstodon