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
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:BackupFileGrowthTest.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'\backupshareFileGrowthTest.bak' WITH COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5 GO :Connect SecondaryNode RESTORE DATABASE [FileGrowthTest] FROM DISK = N'\backupshareFileGrowthTest.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5 GO :Connect PrimaryNode BACKUP LOG [FileGrowthTest] TO DISK = N'\backupshareFileGrowthTest.trn' WITH NOFORMAT, INIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5 GO :Connect SecondaryNode RESTORE LOG [FileGrowthTest] FROM DISK = N'\backupshareFileGrowthTest.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
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
sys.master_files does not reflect the log file autogrowth rate change whereas datafile autogrowth change is reflected correctly?
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.
I try to think about
database_files as scoped to the database, and
master_files as scoped to the instance. I’ve previously thought about this as meaning an affected
df attribute value will be transported on command completion, while an
mf value may require a more physically disruptive reset before it “sticks” on both sides1.
Maybe it’s pro-forma to point out but… line one of each piece of official documentation shows the difference (emphasis mine):
…as stored in the database itself …[
database_files]… is a per-database view.
…as stored in the master database …[
master_files]… is a single, system-wide view.
I don’t think of this as a bug. The secondary replica read of this data is not “incorrect”. It might be more accurate to say it’s “latent”. Have you ever direct seeded an AG only to notice the secondary replica database has a different owner than the primary replica? Perhaps some instance-bound setting like
trustworthy didn’t “take” on the secondary replica and you can’t “fix” it until you failover? It’s even possible to failover and then failback without fixing these attributes in some cases!?
Asking a given source will return the “correct” data for the scope of that source. Taking a look inside at the
object_definition() for each catalog view, we see that
database_files is first pointed at
master_files is at first
A slightly massaged diff of these definitions reveals something like the following:
- MF has 2 extra columns,
- DF expresses a transform not present on MF for the columns
- For all columns other than
state_desc, the base rowset differs as noted above (mf =
sysbrickfiles| df =
- Difference in the predicates
1. Certainly, edge-case testing for different attributes might be a fun way to waste a couple days… The OP specifically mentions log file growth rate as the attribute that’s not carrying. If you could cook up a scenario where the secondary saw the need to autogrow the log but not the primary… well… breaking things can be fun!