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?
Best Answer
VLF Calculations
The number of the VLFs is not different for a manual or automatic growth of the transaction log file.
SQL Server < 2014
Up to version 2014 of SQL Server the algorithm for the number of VLFs is as follows when you create, grow or auto-grow the TLog file:
SQL Server 2014 and higher
As of SQL Server 2014 and higher the algorithm is as follows:
Your calculation for the number of VLFs should be correct.
Is your database set to AUTOSHRINK? Are you executing any other maintenance tasks that could be resizing the TLOG size?
Auto-growth: yes or no?
If you manually grow your TLog file then indeed you could turn off the auto-growth setting. But be aware: What will happen if you are unavailable and the database has to grow? The database will grind to a halt with an error message.
Baseline Size
At some point in time your database will have a TLog size that will have adequate space for the general imports that occur and the everyday workload and the TLog/Diff/Full backups occurring.
Use this baseline size as a starting point when you create a new TLog from scratch. If the database's TLog is really huge then create a TLog with a base size of 8 GB and manually grow in 8 GB steps until you have reached the "baseline" size.
Then leave some additional space on disk to allow for one or two "auto-growths" of 8GB size.
This will leave you with the best settings according to the article Transaction Log VLFs – too many or too few? where they recommend VLFs to be 512 MB in size.
Disk Space
If you don't have infinite disk space, then ensure that the TLog file has a maximum size set, that will not fill you disk to the maximum.
Monitoring
The most important point is to have a monitoring tool in place that monitors the database's "free space", because you never know if a rogue process is going to do things you hadn't anticipated.
Transaction Log Physical Architecture
There is a good article on TechNet that explains the physical architecture of the Transaction Log. This gives you some insight as to why the TLog rolls over or why it might have to grow.
References
Important change to VLF creation algorithm in SQL Server 2014
Initial VLF sequence numbers and default log file size
Transaction Log VLFs – too many or too few?
Transaction Log Physical Architecture