Contents
How do I reduce the initial size of tempdb?
If you do not have extra temp files, and as per method one of the article:
- stop SQL Server.
- from the command prompt: sqlservr -c -f.
- connect to SQL Server.
- ALTER DATABASE tempdb MODIFY FILE (NAME = ‘tempdev’, SIZE = 1) (or whatever size in MB you wish to have)
- restart SQL Server.
How do I limit tempdb size?
If you go into the properties of the database and click on files. You can set it by clicking on the elipses next to Autogrowth / Maxsize. You will set it in MB.
Why does TempDB increase in size?
If there are autogrowth events occurring after you have recycled SQL Server than you might want to increase the size of your tempdb data files. If you make tempdb too small, then the tempdb data or log file will have autogrowth events, as tempdb needs more space.
How can I change the size of my tempdb database?
If you don’t like using that – then simply right-click the TempDB database and change the “initial size” setting there. In either method – issuing that command will either grow or shrink the DB files to the specified size.
What happens if I run shrink on tempdb?
If so, simply replicate the above SHRINKFILE command and include each additional tempdb file. Make sure you do not have any open transactions when running a SHRINKFILE command. Open transactions may cause the operation to fail and could potentially corrupt the tempdb system database.
Is there a way to shrink the DBCC database?
DBCC SHRINKFILE (‘templog’) Shrinks the size of the specified data or log file for the current database. Make sure to include USE [tempdb] or manually specify the database in Management Studio prior to execution. The SHRINKFILE operation can be stopped at any point in the process with all completed work being retained.
Why is tempdb growing faster than SQL Server?
Though numerous factors can lead to excessive growth of the tempdb database I have found the most common factor tends to be related to sorting that requires more memory than has been allocated to the SQL Server, which forces the work to be pushed to tempdb.