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.

I have a database with 160 VLFs. How do I determine whether the VLFs are fragmented? Further, how do I find the percentage of fragmentation?

Another thing that I want to confirm: Are the following 3 statements always true for newly added chunks in SQL server Virtual Log files?

  1. chunks less than 64MB and up to 64MB = 4 VLFs
  2. chunks larger than 64MB and up to 1GB = 8 VLFs
  3. chunks larger than 1GB = 16 VLFs
share|improve this question

3 Answers 3

I would not get hung up on number of VLF's, especially with 160 VLFs. if the VLFs are in 1000's then i'll put it on the list of things to tackle as it can be painful in a 24x7 environment.

The fragmentation of your db log shouldn't be an issue as its sequential. if its been growing in very small chunks, then waiting for a maintenance window, backup the log reduce it down to a few MB then increase in sensible size chunks. These will depend upon projected size of log.

linked a great explanation on VLFs, clear and concise (it also references the size of VLFs to answer your question in terms of chunks)

http://adventuresinsql.com/2009/12/a-busyaccidental-dbas-guide-to-managing-vlfs/

share|improve this answer
    
Actually i Just need to know how can we check fragmentation in VLFs and if A Log file which grow with the rate of greater than 1 gb, than the rule for VLFs is ( new file increment / auto growth rate of file ) * 16 ?? –  AA.SC Jun 26 at 10:51

You're correct about how the VLFs grow in size. Check out the following video from Jes Borland for some more tidbits.

How SQL Server Works: Log File (Video)

share|improve this answer

VLF are totally managed internally by SQL server, I am talking about there size. If you have large amount of VLF's for the database its not a good sign.I guess you are referring to large amount of VLF as VLF fragmentation. High amount of VLF can be attributed to fact that autogrowth settings are not proper for database may be very small and database has to allocate new vlf reach times it grows. Ofcourse VLF size would be small and eventually be many. 160 VLF count does not seems issue to me.Below blog will help you in further understanding

http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

share|improve this answer
    
Yes VLF are managed internally, but I read Kimberly's article and found a statement "Transaction logs can become VERY fragmented"; fragmentation means too many VLFs or there are some other parameters? –  AA.SC Jul 9 at 7:35
    
Yes you are correct that is called Log file fragmentation but its not like SQL server index fragmentation. So just wanted to make it clear. –  Shanky Jul 9 at 8:24
    
Thanks Shanky, actually I was looking for What is VLFs fragmentation. –  AA.SC Jul 9 at 8:28
    
VLF fragmentation ( internal) is nothing but to many VLF in transaction log caused by inappropriate autogrowth setting. External log fragmentation would be drive on which log file resides is fragmneted. –  Shanky Jul 9 at 8:53

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.