We have a two-node SQL Server 2017 AG. There’s a job that looks at autogrowth settings for our databases and makes sure they’re not set to use a percentage. For the past day, its been continually saying it’s updating the settings for a particular database. This hasn’t happened before. Upon investigation, I found sys.master_files on node 01 says the transaction log file for the database will grow in increments of 512MB, but node 02’s sys.master_files says it will grow in increments of 10% Server 02 is the primary. On both replicas, is_percent_growth is cleared in sys.database_files for this database. Looking at file properties in SSMS, both replicas show 512MB autogrowth, most likely because SSMS looks in sys.database_files (a guess on my part).
There haven’t been any failovers on this AG.
- How could there a difference of opinion between sys.master_files and sys.database_files on node 02?
- How did the autogrowth settings for the database get out of sync between the two replicas?
- How can I clear the is_percent_growth flag on node 02 so I don’t have any more false positives? Ideally, I’d like to do this without taking the AG offline.
I’ve read https://stackoverflow.com/questions/4174520/sql-server-sys-master-files-vs-sys-database-files which explains the difference between sys.master_files and sys.database_files, but it doesn’t explain the circumstances here.