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.

I have a 16 Core CPU processor and I have created 8 tempdb datafiles with initial size as 2,000 MB with 10% growth restricted to 10,000 MB. The log file is unrestricted and it is in simple recovery mode.

The tempdb is filling up the space, it is not going through the round robin face. Could you please help me here.

share|improve this question
1  
Why 10%? Why restricted growth? Is each file on a 10 GB drive? Are they all on the same drive? –  Aaron Bertrand Jan 2 at 3:09
add comment

migrated from stackoverflow.com Jan 1 at 20:07

This question came from our site for professional and enthusiast programmers.

1 Answer

You likely need to implement Trace Flag 1117 to enable proportional fill (round-robin) growth on your server. Be aware that TF 1117 affects all databases, not just tempdb.

From technet.com:

Trace flag 1117 (-T1117) can help keep data files the same size in case DB needs to autogrow. Say you have eight DB data files with an initial size of 1000MB and autogrow of 200MB each. If the t database needs to autogrow, the default behavior is for one of these eight files to grow by the specified increment (200MB in this case). This will happen when all of the files are full, and after one file grows, it will be about 83% full (1000MB allocated from 1200MB total). The SQL Server proportional fill algorithm will direct new extent allocations to the file with the most available space, so new extents will be written to the one file that just grew. This defeats the benefit obtained by striping tempdb IO activity (especially extent allocations and de-allocations) across all of the available files.

When -T1117 is enabled, it will cause all the files in a filegroup to autogrow together by their specified autogrow increment. In the DB autogrow scenario described above, instead of one file growing by 200MB, all eight files will grow by 200MB when an autogrow event occurs. This means that all the files will have about the same percent of free space after autogrow, and the benefits of evenly distributing IO will be preserved.

It’s important to understand that –T1117 applies to the entire SQL Server instance, not just to one DB, and it affects all files in the same filegroup in a database. This means that if there are other databases that have multiple files defined for the same filegroup, and an autogrow event occurs in that filegroup, all of the files in the filegroup will autogrow at the same time (by the autogrow increment specified for each file). If there are multiple filegroups in the same database, an autogrow event that occurs in one filegroup will not affect the other filegroups. For a filegroup allocated in a single file, -T1117 has no effect.

When deciding whether to enable –T1117, consider the instance-wide implications and how it may affect other databases that have multiple files for the same filegroup. To avoid blasting your harddrives it is recommended to configure the maximum size of your database files. Always the best option is to presize your files to the assumed maximum.

You may also need to implement trace flag 1118 if you are experiencing tempdb contention. See this question for more info on that.

share|improve this answer
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.