tempdb default size and autogrow

Question

Post reply

tempdb default size and autogrow

dmoldovan

SSCertifiable

Points: 6334
More actions
January 18, 2010 at 3:20 am

#219387

Is there any way to change the default size and autogrow of the tempdb?
I can alter the tempdb size using ALTER DATABASE, however the tempdb will be created using its default settings (8M for the data file, etc...) at a server restart.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Reo Hall of Fame Points: 3774 More actions · Answer 1

why you are restricting the tempdb size?

Ram
MSSQL DBA

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 2

remember to set AUTOGROW by a fixed MB size, and not percentage...

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

dmoldovan SSCertifiable Points: 6334 More actions · Answer 3

Reo - in fact I want to increase the size of the tempdb files (data & log). It seems that the tempdev file needed to grow, and hence an warning message, stating that "Autogrow of file 'tempdev' in database 'tempdb' took 73484 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file." appeared in the SQL error log.

Henrico - yes, I also want to change the autogrow to a fixed value, instead of that 10%.

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 4

Whats your current settings?

Properties tempdb, Files, and have alok at our current config please.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

dmoldovan SSCertifiable Points: 6334 More actions · Answer 5

My initial settings are the default ones, 8MB for the tempdev file and 1MB for thelog file. The autogrow is of 10%.

Currently, the tempdev file is of ~792 MB and the log file is of ~51 MB.

Saravanan T SSCrazy Points: 2506 More actions · Answer 6

Why u want to change the default size and autogrow of the tempdb????

Regards,
Saravanan

Suresh B. SSC-Insane Points: 22986 More actions · Answer 7

dmoldovan (1/18/2010)
Is there any way to change the default size and autogrow of the tempdb?
I can alter the tempdb size using ALTER DATABASE, however the tempdb will be created using its default settings (8M for the data file, etc...) at a server restart.

When you restart SQL Server service, tempdb is recreated with its previous settings.

Test it.