What happens to proportinal fill once data files reach their limit?



  • 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?



  • 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; 
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2