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.
Clearly drafted and very informative. Thanks for the post.