Question: How to Grow All Files in a File Group Equally?
Answer: If you are a regular reader of this blog, you would have read my earlier blog post writing about this subject over here: SQL Server 2016 – Introducing AutoGrow and Mixed_Page_Allocations Options – TraceFlags. However, let us try to get an answer to our question in this blog.
Trace Flag 1117, this is about how the growth happens to data files inside a filegroup. It is recommended to have the same data file sizes within a given Filegroup. Also, when the files grow, it is recommended to grow them uniformly. Another great addition was done to the SQL Server 2016’s ALTER command which can easily get unnoticed.
In SQL Server 2016 and 2017, you can use the following command to enable.
ALTER DATABASE Adventureworks2016 MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES GO
Well, if you are using an earlier version of SQL Server, you can enable trace flag 1117 by going to start up parameter.
Here is step by step direction to do the same.
First of all, go to SQL Server 2017 Configuration Manager.
Next right click on the SQL Server services and go to properties.
While you are at SQL Server Services Properties go to StartUp Parameters and add the trace flag you want to add when you want to start your SQL Server
Please remember this is a startup trace flag and will be effective when your SQL Server services restart next time.
Reference: Pinal Dave (https://blog.sqlauthority.com)
