數據庫日誌文件增長更改不會反映在輔助副本 sys.master_files

環境：Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Apr 20 2015 17:29:27 版權所有 (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不反映日誌文件自動增長速率變化而數據文件自動增長變化反映正確？

這是一個已知問題，我懷疑master在次要實例上不會立即寫入，並且可能在該實例成為主要實例之前根本不會寫入。根據 Nic Cain 在 2015 年送出的這個錯誤，這個問題仍在“審查中”：

• 用於 TLOG 增長的 sys.master_files 目錄視圖未在 AG 輔助節點上更新

在解決這個問題之前，我建議使用sys.database_files而不是sys.master_files，儘管我知道這並不總是很方便。

另請參閱此論壇主題、此論壇主題和Nic 的有關此問題的部落格文章。

引用自：https://dba.stackexchange.com/questions/220376