I wonder if the internal methods of growing a file using
ALTER DATABASE testdb
MODIFY FILE
(
NAME = testdb_Log
, SIZE = 40960MB --40 GB
)
work different then letting the file autogrow to the same size over time?
Background of my question: I face very high (>10K) VLF counts on several Log files of different live DBs with full recovery that are sized around 100 GB.
While testing strategies to get this fixed, I resized a Log file to 16 MB having 4 VLFs and let it grow to 40960 MB then with an AUTOGROW setting of 100 MB. Afterwards I had 20 VLF in the file.
My expectation was to see additional
(40960 MB - 16 MB) / 100 MB * (8 VLF per autogrow) = 3276 VLF in the file.
Instead I now have only 20. Why?
Second part of my question: So far I understood that an inappropriate AUTOGROW setting cause the high VLF counts, that is defenitely the case in our dbs where this setting has not been looked after for a long period of time. All the articles explaining how to handle this situation advice to choose a proper value for AUTOGROW. But if I am right assuming that if the manual grow process (first part of this post) does not even use the autogrow setting, and I grow the log file this way to a proper size that does not require any automatic growing, then the autogrow settings is not that important at all afterwards?
Third part of my question? Is the VLF count of 20 on a 40GB log file now too low? Is this the reason I read we should increment the file back up again in increments? What VLF count might be a GOOD (performant) number for a 40 G or 80 G log file?