Sql-server – Database Log File Growth change does not reflect on secondary replica sys.master_files

auto-growthsql serversql server 2014

Environment: Microsoft SQL Server 2014 – 12.0.4100.1 (X64) Apr 20 2015 17:29:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

When I change the log file growth rate on primary replica, secondary replica database get that change and I can verify from GUI and sys.database_files view. But same change does not reflect in sys.master_files view.

Set up code

:Connect PrimaryNode

IF EXISTS(SELECT name FROM sys.databases
  WHERE name = 'FileGrowthTest]')
  DROP DATABASE FileGrowthTest
GO
CREATE DATABASE [FileGrowthTest]
 ON  PRIMARY 
( NAME = N'FileGrowthTest', FILENAME = N'L:\FileGrowthTest.mdf' , SIZE = 4096KB , FILEGROWTH = 10%)
 LOG ON 
( NAME = N'FileGrowthTest_log', FILENAME = N'F:\FileGrowthTest_log.ldf' , SIZE = 4096KB , FILEGROWTH = 10%)
GO

BACKUP DATABASE [FileGrowthTest] TO  
    DISK = N'E:\Backup\FileGrowthTest.bak' 
    WITH NOFORMAT, NOINIT,  NAME = N'FileGrowthTest-Full Database Backup', 
    SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect PrimaryNode

USE [master]

GO

ALTER AVAILABILITY GROUP [TestAG]
ADD DATABASE [FileGrowthTest];

GO

:Connect PrimaryNode

BACKUP DATABASE [FileGrowthTest] TO  DISK = N'\\backupshare\FileGrowthTest.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5

GO

:Connect SecondaryNode

RESTORE DATABASE [FileGrowthTest] FROM  DISK = N'\\backupshare\FileGrowthTest.bak' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO

:Connect PrimaryNode

BACKUP LOG [FileGrowthTest] TO  DISK = N'\\backupshare\FileGrowthTest.trn' WITH NOFORMAT, INIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5

GO

:Connect SecondaryNode

RESTORE LOG [FileGrowthTest] FROM  DISK = N'\\backupshare\FileGrowthTest.trn' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO

:Connect SecondaryNode


-- Wait for the replica to start communicating
begin try
declare @conn bit
declare @count int
declare @replica_id uniqueidentifier 
declare @group_id uniqueidentifier
set @conn = 0
set @count = 30 -- wait for 5 minutes 

if (serverproperty('IsHadrEnabled') = 1)
    and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
    and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)
begin
    select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'TestAG'
    select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id
    while @conn <> 1 and @count > 0
    begin
        set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
        if @conn = 1
        begin
            -- exit loop when the replica is connected, or if the query cannot find the replica status
            break
        end
        waitfor delay '00:00:10'
        set @count = @count - 1
    end
end
end try
begin catch
    -- If the wait loop fails, do not stop execution of the alter database statement
end catch
ALTER DATABASE [FileGrowthTest] SET HADR AVAILABILITY GROUP = [TestAG];

GO

GO

Both file has 10% growth rate now.
Changing to fixed value.

USE [master];
GO
ALTER DATABASE FileGrowthTest MODIFY FILE (NAME='FileGrowthTest', FILEGROWTH = 256MB);
ALTER DATABASE FileGrowthTest MODIFY FILE (NAME='FileGrowthTest_log', FILEGROWTH = 128MB);
GO

Change is visible on secondary replica node while using sys.database_files view for both data and log files.

USE [FileGrowthTest];
GO
SELECT name AS file_name,
type_desc AS file_type,
growth AS current_percent_growth
FROM sys.database_files
WHERE is_percent_growth=1

But using sys.master_files view only datafile change is visible. Log file growth still shows 10%.

SELECT d.name as database_name,
    mf.name as file_name,
    mf.type_desc as file_type,
    mf.growth as current_percent_growth
FROM sys.master_files mf (NOLOCK)
JOIN sys.databases d (NOLOCK) on mf.database_id=d.database_id
WHERE is_percent_growth=1
AND d.name='FileGrowthTest'
GO

Why sys.master_files does not reflect the log file autogrowth rate change whereas datafile autogrowth change is reflected correctly?

Best Answer

This is a known issue, I suspect that master on the secondary won't be written to immediately, and perhaps not at all until that instance becomes the primary. The issue is still "under review" according to this bug filed by Nic Cain back in 2015:

Until this is addressed, I recommend using sys.database_files instead of sys.master_files, though I know this isn't always convenient.

Also see this forum thread, this forum thread, and Nic's blog post about this issue.