I recently came across a bug with SQL Server and Availability Groups whereby catalog view data is incorrectly reported on all secondary replicas.

This bug has the potential for putting the availability of your environment at risk as reporting around capacity could be calculated incorrectly.

Very similar to an old bug with mirroring in SQL Server 2008 R2 this issue affects the sys.master_files catalog view. In this view you can see information around the names, sizes, growths, and locations of all of the files on all of the databases in your server.  This bug, when encountered, leaves the information in sys.master_files on an Availability Group secondary replica in an inaccurate state when filegrowth settings are changed on the primary replica.

Let’s take a local lab example (SQL Server 2014)

I have an AG which contains the AdventureWorks2014 database. There is a primary, and two secondary replicas.  By using a server groups in SSMS I can query each of them from a single command to capture file information.

Here I am pulling the data from sys.master_files, and AdventureWorks.sys.database_files. Both views return the same (correct) information:

After running a script on the AG primary replica to change the filegrowth settings on the data, and the log file, I would expect to see this information reflected in all locations.

ALTER DATABASE AdventureWorks2014
MODIFY FILE (NAME = AdventureWorks2014_Log, FILEGROWTH = 100MB);

ALTER DATABASE AdventureWorks2014
MODIFY FILE (NAME = AdventureWorks2014_Data, FILEGROWTH = 200MB);

As you can see sys.database_files correctly reflects the changes on all servers. In sys.master_files the data file change is correctly set, however, the log file change is only accurate on the primary replica.

If you happen to be reporting disk usage, and forecasting how database file growth could impact your available storage then you could be using bad data to figure things out.

There is a workaround for this, that is to restart the secondary replicas, at which point they will pick up the change, but until then they’ll be wrong.  Note, that even after failing over the primary to a secondary the information will be incorrect in sys.master_files until you make another change, or restart the service.

I’ve filed a bug on Microsoft Connect (sys.master_files catalog view for TLOG growth is not updated on an AG secondary) in the hopes that this can get resolved. Please go and upvote if you also have this issue.

Currently I’ve been able to repo on 2012, and 2014. Testing on 2016 CTP 3.0 to come.