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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
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.
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).