Database Log File Growth change does not reflect on secondary replica sys.master_files

3 votes
3 answers
619 views
sql-server sql-server-2014 auto-growth
                          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?

                        
Asked by SqlWorldWide (13707 rep)
Oct 17, 2018, 04:53 PM
Last activity: Dec 4, 2023, 06:40 AM
Database Log File Growth change does not reflect on secondary replica sys.master_files

Related Questions
