Share via

How to: Change the tempdb file size

  • Article

You could have been accidentally increased your tempdb data file to more than it actually needs and you have difficultly to reduce the size.  Probably you have tried to resize it from the database properties or shrink the file, unfortunately it doesn't make any changes to the file.  The files size remain big.

For instance, your tempdb has 1GB, you accidentally increase it to 81GB which you actually plan to increase it to 8GB.  Here is the solution.

First, you should reduce your tempdb file to the size smaller than your desire size.

DBCC SHRINKFILE (N'tempdev', '1024000KB'); --1GB

After that, you can change the tempdb file size to your desire file size.

ALTER DATABASE [tempdb] MODIFY FILE (
NAME = N'tempdev',
SIZE = 8388608KB ); --8GB