Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Following on from a recent question that I asked on Tempdb. I was wondering how to correctly set the autogrowth property when adding and moving the tempdb files?

I ask because I would like to let SQL Server use the round-robin algorithm to spread the workload out across each of the files. I understand that if the files are not of equal size, then SQL Server will use the file with the most space, thus increasing the chances on causing latch contention. Is my understanding here correct?

Cheers,

share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted

You could run this against each file, replacing each n with the size and growth amounts:

ALTER DATABASE tempdb
MODIFY FILE
(
  NAME       = tempdev,
  SIZE       = nMB,
  FILEGROWTH = nMB
);

ALTER DATABASE tempdb
MODIFY FILE
(
  NAME       = tempdev2,
  SIZE       = nMB,
  FILEGROWTH = nMB
);

...

Now to ensure that they all grow at the same time (instead of only one of them growing, e.g. in response to a large spill or other operation), then you can enable trace flag 1117, but keep in mind that this flag applies to all databases, so if tempdb isn't your only database with more than one data file, you'll want to test this behavior. More info:

http://blogs.technet.com/technet_blog_images/b/sql_server_sizing_ha_and_performance_hints/archive/2012/02/09/sql-server-2008-trace-flag-t-1117.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/Tempdb-configuration-survey-results.aspx

share|improve this answer
    
Thanks for the links. It would be great if that trace flag could be configured for just one database –  Stuart Blackler Jul 15 '12 at 19:56
    
@StuartBlackler yes, but if you size your tempdb large enough, you shouldn't need to rely on the autogrow at all - it should only happen in an extremely abnormal and unplanned scenario. After all, for what purpose are you saving the space that tempdb might use? You can't really use it for other things, so why not just pre-size tempdb to the max? –  Aaron Bertrand Jul 15 '12 at 20:00
    
Typically in the environments I work in, SQL Server is generally placed on a multi-purpose server. So SBS might be on the box as well so I have to keep these things in mind. I am definitely an accidental dba (developer by trade) and your comments are much appreciated. I will have to come up with sensible defaults for each environment. Thanks –  Stuart Blackler Jul 15 '12 at 20:10
    
@StuartBlackler but still I hope my point hits home - if you're reserving space for SBS and it uses that space, you're screwed when tempdb has to autogrow anyway. So leaving a volume half empty and having a race to see which database uses the space first is a very dangerous gamble. –  Aaron Bertrand Jul 15 '12 at 20:12
    
Yes it does, thanks for the insight. :) –  Stuart Blackler Jul 15 '12 at 20:17
add comment

Just an additional note, be sure to turn on instant file initialization. That means that if your databases grow, they grow much faster.

share|improve this answer
    
Got that down on my to-do list. Sys-admins are going to love me on Tuesday when im back in the office :D Thanks for the link. –  Stuart Blackler Jul 15 '12 at 20:18
    
+1 it's a good point, I'm so used to working in environments where this was configured long ago, it's not the first thing that comes to mind. :-) –  Aaron Bertrand Jul 15 '12 at 20:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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