0

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

  • Does the instance have trace flag 1117 and 1118 turned on? 1117 ensures files grow at the same rate. It sure looks like these files aren't growing at the same rate. I realize you said in your question there were no growth events, however, there clearly has been some growth on 'tempdb15' and 'tempdb16'. – Max Vernon Sep 15 '14 at 20:31
  • Currently, both of those trace flags are disabled. Would that not allow me to see if there were any autogrow events? – Kris Gruttemeyer Sep 15 '14 at 20:34
  • Just added to the bottom for ya. – Kris Gruttemeyer Sep 15 '14 at 20:38
  • Isn't that trace flag instance-wide, though? Not confined only to the scope of the database? Also, in running the query provided by your link, it's only returning information on master, not on any other DBs. – Kris Gruttemeyer Sep 15 '14 at 20:42
  • Yes, TF1117 is instance wide. There is an open connect item to have Microsoft setup a flag for just the tempdb, here: connect.microsoft.com/SQLServer/feedback/details/781198/… – Max Vernon Sep 15 '14 at 20:45
2

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.