If there are lot number of VLF then it will effect the performance of the queries. I saw that solution is to shrink the log file. But shrinking the file is also bad. So how to take control of whole thing? What is the optimal number of VLF in a database?
asked Aug 23, 2013 at 7:19
Add a comment
|
2 Answers
"But shrinking the file is also bad"
Shrinking a data file: usually bad, mostly due to the havoc it wreaks on the allocation layout and results in increased fragmentation. Why you should not shrink your data files.
Shrinking a log file: provided the log has to shrink (it did grow beyond reason), shrinking it is mostly harmless. It should not be abused though as excessive shrinking will just trigger new growth when the file needs to expand for legitimate reasons. See How to shrink the SQL Server log.
If there are lot number of VLF then it will effect the performance of the queries
Strictly speaking it will affect the performance of recovery, not the performance of queries. Queries will largely ignore the log (except queries that query the log, of course). Even writes (DML) will not suffer too much from a large number of VLFs. Is recovery that is impacted, really really really seriously, as to make it imperative to keep the number of VLFs in check.
answered Aug 23, 2013 at 7:40
If you do not have the transaction log under control, make sure that the auto grow setting is not set to the default 10%. That is the reason why you get so many VLF's, a lot of small auto grows and the fact that the transaction log was not properly sized from the beginning.
To remedy the problem (during off hours since this is a blocking operation).
- Shrink the T-log to a minimum size.
- Grow it back up to a size where it does not have to autogrow immediately, a size that handles your workload under normal circumstances.
- Make sure that the auto grow setting is set to a fair size, do not forget that instant file initialization does not work for the transaction log.
