1

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.

Questions

  1. How could there a difference of opinion between sys.master_files and sys.database_files on node 02?
  2. How did the autogrowth settings for the database get out of sync between the two replicas?
  3. 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.

1 Answer 1

2

Here's a guess:

When you seed (auto or through a backup) the database, the info from sys.database_files is copied into sys.master_files on the other end (which lives in a table in the master database).

My guess is that a later change for the file properties, which is stored in (what you see though) sys.database_files is not reflected in sys.master_files. Perhaps some changes triggers the state to be copied into sys.master_files and some change don't? And perhaps this has been improved in later versions?

Sorry to be so vague, I don't have the environment here to test this. I would do some changes to the ldf file and see if any of those changes causes sys.master_files at the other end to be updated.

Increase the file size. Shrink it (just 1 MB). Change max size. Etc. One at a time, of course.

1
  • 1
    Tweaking the size of the log works and forced a sync between the two replicas. Sorry it took so long to follow up on this!
    – S M
    May 7, 2021 at 22:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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