Understanding the is_autogrow_all_files setting

Starting with SQL Server 2016, this setting is enabled by default for tempdb.

Below is a run down on how it works.

/* use tempdb */
USE [tempdb]
GO

/* file groups shows is_autogrow_all_files as enabled*/
SELECT
 name
,is_autogrow_all_files 
FROM sys.filegroups;
is_autogrow_all_files enabled
/* current size */
SELECT 
 name
,physical_name
,type_desc
,(growth* 8)/1024 AS growth_mb
,(size * 8)/1024 AS size_mb
FROM sys.database_files
ORDER BY name;
Ideally all ROWS are the same size
/* change 1 file size */
ALTER DATABASE [tempdb] MODIFY  FILE ( NAME = N'tempdev', SIZE = 10MB );

/* current size */
SELECT 
 name
,physical_name
,type_desc
,(growth* 8)/1024 AS growth_mb
,(size * 8)/1024 AS size_mb
FROM sys.database_files
ORDER BY name;
Modifying 1 file size has no impact on others
/* grow database */
CREATE TABLE #t1 (c1 nchar(4000));
GO
INSERT INTO #t1 (c1) VALUES ('1')
GO 5000

/* current size */
SELECT 
 name
,physical_name
,type_desc
,(growth* 8)/1024 AS growth_mb
,(size * 8)/1024 AS size_mb
FROM sys.database_files
ORDER BY name;
Auto growth kicked in, but still a mismatch in sizes.
Notice that LOG file is not impacted.

Take away from this should be to ensure that your tempdb data files are the same size on startup and use the same growth settings.

You can use sys.master_files to check your tempdb initial size settings.

Leave a Reply