数据库日志文件增长更改不会反映在辅助副本 sys.master_files 上

环境：Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Apr 20 2015 17:29:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) o​​n Windows NT 6.3 (Build 9600: ) (Hypervisor)

当我更改主副本上的日志文件增长率时，辅助副本数据库会得到该更改，我可以从 GUI 和sys.database_files视图中进行验证。但同样的变化并没有反映在sys.master_files视野中。

设置代码

: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

现在这两个文件都有10%的增长率。更改为固定值。

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

sys.database_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

但是使用sys.master_files仅查看数据文件更改是可见的。日志文件增长仍然显示 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

为什么sys.master_files不反映日志文件自动增长速率变化而数​​据文件自动增长变化被正确反映？