Sql-server – SSMS 2008 / 2014 showing different values for TempDB file sizes

sql serversql-server-2008-r2tempdb

Here's a fun one. Last week I reconfigured the initial file sizes in our tempdb database to 1GB each. We had an event a few weeks back that locked up our server due to a table with no indexes getting absolutely hammered. This caused our 3 of our tempdb files to grow. I re-sized them using SSMS 2014 and they should have reset to their configured size this past Sunday when we rebooted the server. They didn't and now I'm seeing a difference in the information SSMS is returning for those files.

Our server is 2008 R2, here are the different SSMS views:

2008:

enter image description here

2014:

enter image description here

Here are the files as they currently sit on the server itself:

enter image description here

It seems that SSMS 2014 is returning the correct information, but SSMS 2008 is not. Is this a known issue that anyone has seen before? If so, what's causing it? Does 2014 attempt to store this information differently than 2008 R2 and negate the 2008 configured values?

Now, there were no auto-grow events that happened once the server was restarted (checked the default trace and there were no events) Given this information, what do you think my best course of action is to get these files re-sized appropriately. I'm totally fine with growing all the files to 2GB but those tempdb files are hardly being used (<5% usage on all of them).

The servers are not on fire or anything and production is running fine but we definitely want all those tempdb files configured to the same size.

EDIT: Result of requested query

enter image description here

Best Answer

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:

Related Question