By: Ahmad Yaseen | Comments (1) | Related: > Database Configurations
Problem
SQL Server database auto-growth is a process in which the SQL Server Engine expands the size of the database file when that file runs out of space. The size expansion amount is based on the database files auto-growth settings that are configured at the database level. These database file auto-growth settings include growing the file by a specific size, percentage of the current file size or no growth performed at all. The best practice for these options is growing the files with a fixed amount to have better control on the disk drive consumption process.
Auto-growth is an expensive process, where SQL Server will hold up the database processing during the auto-growth process, slowing the database response time for the incoming commands runs against that database. This process is performed on each database file separately, resulting in database files with different sizes. Is there a way to enforce all database files to be expanded at the same time?
Solution
Generally speaking with a large database, you may need to create more than one data file in the same filegroup. When the data is written to that database, a round robin insert process will be performed across these files, in which a piece of data will be written to each file until the data is fully written to the files. SQL Server writes data to the database files depending on the amount of free space in each data file, using the Proportional Fill algorithm. If the data file runs out of space, the auto-growth event will be triggered to expand the file size. In this way, one file will be larger than the other one, which results in an unbalanced data distribution across these files. To ensure an even distribution of the data across the database files, the database files should have the same size.
Using Trace Flag 1117 in SQL Server version earlier to SQL Server 2016, all database files will expand at the same time when one of these files runs out of space, taking into consideration that the auto-growth is configured on the database. However, this trace flag is a global level trace flag, which means that it will be applied to all databases hosted on the SQL instance, having the auto-growth enabled on the databases can potentially consume the disk drive space faster.
SQL Server 2016 introduces a new feature in which you can control the auto-growth process of the database files within the same filegroup at the database level, to be at singe file at a time or all database files at the same time. This occurs if the auto-growth is configured on that database, using one of the new ALTER DATABASE options AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES.
To understand this new feature practically, we will create two new databases; each database has three data files and one log file with 4096KB as an initial size for all the database files, and auto-growth is enabled on all the database files. The script that is used to create the first database is the following:
CREATE DATABASE [AutoGrowthTest] ON PRIMARY ( NAME = N'AutoGrowth_1', FILENAME = N'D:\Data\AutoGrowth_1.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB ), ( NAME = N'AutoGrowth_2', FILENAME = N'D:\Data\AutoGrowth_2.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ), ( NAME = N'AutoGrowth_3', FILENAME = N'D:\Data\AutoGrowth_3.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'AutoGrowth_1_log', FILENAME = N'D:\Data\AutoGrowth_1_log.ldf' , SIZE = 4096KB , FILEGROWTH = 10% ) GO
The script to create the second database will be same as the previous one except for the ALTER DATABASE statement that is used to enable the AUTOGROW_ALL_FILES feature on the PRIMARY filegroup of that database. The script is as follows:
CREATE DATABASE [AutoGrowthTest_WithAUTOGROW_All] ON PRIMARY ( NAME = N'AutoGrowthAGAll_1', FILENAME = N'D:\Data\AutoGrowthAGAll_1.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB ), ( NAME = N'AutoGrowthAGAll_2', FILENAME = N'D:\Data\AutoGrowthAGAll_2.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ), ( NAME = N'AutoGrowthAGAll_3', FILENAME = N'D:\Data\AutoGrowthAGAll_3.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'AutoGrowthAGAll_1_log', FILENAME = N'D:\Data\AutoGrowthAGAll_1_log.ldf' , SIZE = 4096KB , FILEGROWTH = 10% ) GO ALTER DATABASE [AutoGrowthTest_WithAUTOGROW_All] MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES; GO
We can now query the sys.database_files and sys.filegroups system objects to check the database files properties:
USE AutoGrowthTest
GO
SELECT
DB_NAME() DatabaseName,
DBF.name AS FileName,
FileG.name as FileGroupName,
FileG.is_autogrow_all_files AutoGrowthEnable
FROM sys.database_files AS DBF
JOIN sys.filegroups AS FileG
ON DBF.data_space_id = FileG.data_space_id
GO
USE AutoGrowthTest_WithAUTOGROW_All
GO
SELECT
DB_NAME() DatabaseName,
DBF.name AS FileName,
FileG.name as FileGroupName,
FileG.is_autogrow_all_files AutoGrowthEnable
FROM sys.database_files AS DBF
JOIN sys.filegroups AS FileG
ON DBF.data_space_id = FileG.data_space_id
The result will show us the is_autogrow_all_files property which specifies if the database files within the same filegroup will grow together or is not.
Now, we will create a simple table that contains employee information in the two test databases as follows:
USE AutoGrowthTest GO CREATE TABLE Employees ( EmpID int IDENTITY(1,1), EmpName NVARCHAR(500) ) GO USE AutoGrowthTest_WithAUTOGROW_All GO CREATE TABLE Employees ( EmpID int IDENTITY(1,1), EmpName NVARCHAR(500) )
Then we will insert 260,000 records into these two tables to have a test scenario:
INSERT INTO AutoGrowthTest.dbo.Employees VALUES ('Mohammad Yaseen')
GO 260000
INSERT INTO AutoGrowthTest_WithAUTOGROW_All.dbo.Employees VALUES ('Mohammad Yaseen')
GO 260000
We can then check the first database files sizes after inserting the data using the sp_helpfile system stored procedure:
USE AutoGrowthTest GO EXEC Sp_helpfile
You can see that the database files were expanded separately due to an unbalanced round-robin writing process, this results in different sizes for these files as shown below:
We can check the second database files sizes, after inserting the data using the sp_helpfile system stored procedure:
USE AutoGrowthTest_WithAUTOGROW_All GO EXEC Sp_helpfile
You can see that the database files were expanded at the same time, resulting in the same size with even data distribution across the files as shown below:
Let us perform another test. This time we will create a new filegroup on the AutoGrowthTest_WithAUTOGROW_All database and add two new data files under that filegroup, with an initial size of 6144KB:
USE [master] GO ALTER DATABASE [AutoGrowthTest_WithAUTOGROW_All] ADD FILEGROUP [TestFG] GO ALTER DATABASE [AutoGrowthTest_WithAUTOGROW_All] ADD FILE ( NAME = N'AutoGrowthAGAll_5', FILENAME = N'D:\Data\AutoGrowthAGAll_5.ndf' , SIZE = 6144KB , FILEGROWTH = 1024KB ) TO FILEGROUP [TestFG] GO ALTER DATABASE [AutoGrowthTest_WithAUTOGROW_All] ADD FILE ( NAME = N'AutoGrowthAGAll_6', FILENAME = N'D:\Data\AutoGrowthAGAll_6.ndf' , SIZE = 6144KB , FILEGROWTH = 1024KB ) TO FILEGROUP [TestFG] GO
We can query the sys.database_files and sys.filegroups system objects again to check the new filegroup:
USE AutoGrowthTest_WithAUTOGROW_All
GO
SELECT
DB_NAME() DatabaseName,
DBF.name AS FileName,
FileG.name as FileGroupName,
FileG.is_autogrow_all_files AutoGrowthEnable
FROM sys.database_files AS DBF
JOIN sys.filegroups AS FileG
ON DBF.data_space_id = FileG.data_space_id
The two new data files under the TestFG filegroup are shown below and we can see the is_autogrow_all_files attribute is not enabled by default for these files:
We will create a new table called Employee_FG2 and put into the TestFG filegroup:
USE AutoGrowthTest_WithAUTOGROW_All GO CREATE TABLE Employees_FG2 ( EmpID int IDENTITY(1,1), EmpName NVARCHAR(500) ) ON TestFG
We can check the database files sizes before inserting the data using the sp_helpfile system stored procedure:
USE AutoGrowthTest_WithAUTOGROW_All GO EXEC Sp_helpfile
As shown below, all database files have a 6144KB size with 1024KB fixed auto-growth amount:
Then we will insert a different data amount to the old and new tables. We will insert 260,000 records to the first table and 260 records to the second table:
INSERT INTO AutoGrowthTest_WithAUTOGROW_All.dbo.Employees VALUES ('Mohammad Yaseen')
GO 260000
INSERT INTO AutoGrowthTest_WithAUTOGROW_All.dbo.Employees_FG2 VALUES ('Mohammad Yaseen')
GO 260
Now we will check the database files sizes again after inserting the data using sp_helpfile:
USE AutoGrowthTest_WithAUTOGROW_All GO EXEC Sp_helpfile
We can see below that the database data files 1, 2 and 3 in the PRIMARY filegroup are expanded to the same size and the database data files 4 and 5 in the TestFG filegroup are still the same size since we didn't insert much data into the table on the TestFG filegroup, therefore an auto-growth was not necessary. This shows that the files within different filegroups are expanded as needed and the is_autogrow_all_files feature works at the filegroup level for the database:
Next Steps
- Check SQL Server 2016 Trace Flags 1118 and 1117 for Page Allocations.
- Check also Auto Grow, Auto Shrink and Physical File Fragmentation in SQL Server.
About the author
Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
