SQL Server 2008 R2 – TempDB Growth Issue

10MB is ridiculous, IMHO. Is your I/O subsystem that slow where limiting growth to such a small increment is necessary? Ideally you should not be observing AutoGrow at all, by sizing the log large enough to handle your largest transactions anyway. And only use AutoGrow as a crutch to protect you from abnormal events, such as this one, in which case it should be rather large.

And no, I would not reduce your log file to 200MB, and I'm not sure how that is "more sensible." Growth events are expensive, particularly with the log file, since the expanded portion of the file has to be zeroed out. You want to size the log file in such a way that you will avoid any predictable growth events. If you will be performing another similar index rebuild in the future, I don't understand what you would gain by shrinking the log file to 200MB in the meantime. Are you going to lease the freed up space out to someone else, then kick them out when your log needs the space again? I would say leave the log at 32GB (shrinking it and expanding it one event, as the script you pointed out suggested) and monitor for AutoGrow events (these are captured in the default trace, so you don't have to set anything up, just look at it once in a while). (On more modern versions, should think about using Extended Events for tracking growth events instead.) This will tell you if you need to consider making it even bigger.

There is no prize for smallest log file - you want it to be large enough to accommodate all of your normal (and abnormal) log activity without having to grow.

To be certain you are configuring tempdb file sizes as intended, you should use T-SQL instead of the SSMS user interface.

To resize files, you can use something like this:

ALTER DATABASE tempdb
MODIFY FILE (NAME=tempdev, SIZE=1GB, GROWTH=1GB);

You'd need to execute this once for each logical file in tempdb. NAME=tempdev_2, NAME=tempdev_3 etc.

I'd suggest using a T-SQL query to obtain information about the file sizes, as in:

SELECT DatabaseName = d.name
    , DBFileName = mf.name
    , DBInitialSize = mf.size
    , Growth = mf.growth
    , GrowthIsPercentage = mf.is_percent_growth
    , FileType = mf.type_desc
FROM master.sys.master_files mf
    INNER JOIN master.sys.databases d ON mf.database_id = d.database_id
WHERE d.name = 'tempdb'
ORDER BY d.name, mf.name;

As mentioned in the comments, you should certainly look into the possibility of using Trace Flag 1117 and 1118, if you are concerned about tempdb performance; ostensibly this is the root-cause of your questions about tempdb.

Aaron Bertrand has a great article about using the SSMS Interface vs T-SQL at:

• https://sqlblog.org/2009/10/14/bad-habits-to-kick-using-the-visual-designers