Is the filegroup's individual file expansion a default and can this be changed to allow all to expand together?



  • Link: https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver15#file-and-filegroup-fill-strategy

    For example, a filegroup is made up of three files, all set to automatically grow. When space in all the files in the filegroup is exhausted, only the first file is expanded. When the first file is full and no more data can be written to the filegroup, the second file is expanded. When the second file is full and no more data can be written to the filegroup, the third file is expanded. If the third file becomes full and no more data can be written to the filegroup, the first file is expanded again, and so on.

    Is this the default behavior and can this be controlled such that all files grow together?



  • What is described is the default behaviour except for the tempdb database. Tempdb is configured (as of SQL Server 2016) so that if one file grow, then all file grows.

    You can get the behavior for other databases as well, but you have to turn it on, at the filegroup level. Example:

    ALTER DATABASE Adventureworks MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES
    

    Prior to SQL Server 2016, we had trace flag 1117 to enable this. 1117 is a dummy as of SQL Server 2016, it doesn't do anything nowadays.




Suggested Topics

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