0

Taking the example of data files (user db or temp db), assuming initial db size is 1GB and we have 3 data files.

Proportional fill will allow data to enter into the 3 files such that it allow max parallelism because of proportional fill algorithm.

However, once the data reaches 1GB in each file, and assuming auto growth is set to 100MB, then will proportionally fill algorithm stop working because only 1 file's 100MB is available (free) for use?

Is there way around this for example by growing all data files together by 100MB even when only 1 of them is full?

1 Answer 1

1

It is a best practice to enable the Trace Flag 1117 to allow auto growth on all database filegroup files together. From SQL Server 2016 that TF is by default for tempdb and it is no more required.

But enabling the Trace Flag 1117 didn’t force all files under the same filegroup to grow together.

To do that on al databases, SQL Server 2016 introduces a new ALTER DATABASE option that enforces all files under the same filegroup to grow together, this option is called AUTOGROW_ALL_FILES. The default value is AUTOGROW_SINGLE_FILE.

ALTER DATABASE xxxxxx 
MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES; 
3
  • In 1st paragraph you say it's default. Then why does it need enabling?
    – variable
    Commented Mar 8, 2022 at 11:33
  • 1
    He says that’s it’s the default in 2016. If you’re using an older version of SQL, you’d still need to enable it. Commented Mar 8, 2022 at 12:21
  • The behavior is only on by default for tempdb. For other databases you need that file group property. And, the trace flag is a dummy. All this is as of 2016. Commented Mar 8, 2022 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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