Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

"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.

share|improve this answer
add comment

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).

  1. Shrink the T-log to a minimum size.
  2. Grow it back up to a size where it does not have to autogrow immediately, a size that handles your workload under normal circumstances.
  3. 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.

A Busy/Accidental DBA’s Guide to Managing VLFs

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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