2

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?

  • Thank you again. I did not notice earlier but read both now. Not a straight forward reasoning, Why one will update and not the other one? Asynchronous update of the view also does not make sense as only way to get correct data is failover to secondary replica. – SqlWorldWide Oct 17 '18 at 18:31
  • I'm not sure, how long did you wait? Could be a once-a-day thing, or not until some other change triggers a synchronization. In any case, if you want it to be reliable, you need to use sys.database_files. Why it's not reliable is secondary. – Aaron Bertrand Oct 17 '18 at 18:47
2

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.

1

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 sys.sysprufiles whereas master_files is at first sys.sysbrickfiles.

A slightly massaged diff of these definitions reveals something like the following:

Briefly...

  1. MF has 2 extra columns, database_id and credential_id
  2. DF expresses a transform not present on MF for the columns size and physical_name
  3. For all columns other than type_desc and state_desc, the base rowset differs as noted above (mf = sysbrickfiles | df = sysprufiles )
  4. 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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.