A SQL Server database consists of at least two types of file on disk – a transaction log file, and at least one data file where the data and indexes persist. The best practice is to have one transaction log on a disk (or disks) dedicated to database transaction logs. For data files, it is strongly recommended leave the primary filegroup for database metadata operations, and create at least one secondary filegroup. This secondary filegroup should be set to be default so new data objects are created here, and having four datafiles within this filegroup would be a good start. These four datafiles should be identically sized, perhaps placed on several disks depending on the requirements of the application. All these files should have autogrowth enabled to allow SQL Server to extend as the database grows – the autogrowth setting should be the same across all the datafiles, and be proportionate for the size of the database.
As data is put into this database, proportional fill will spread the data relatively evenly over these evenly sized datafiles. A traditional challenge for a DBA is that if autogrowth is enabled, one datafile will extend – and the other three will not, leading to imbalance. And as the extended datafile has more free space, proportional fill will place more data in that datafile, and the imbalance will grow.
In SQL Server 2016 or later, you can set SQL to autogrow all datafiles to grow together – this is set at the individual filegroup level (not datafile) within a specified database. Unfortunately, it is not exposed within Management Studio in either SQL Server 2016 or SQL Server 2017.
ALTER DATABASE TEST MODIFY FILEGROUP SECONDARY AUTOGROW_ALL_FILES
The filegroup property ‘AUTOGROW_ALL_FILES’ has been set.