6

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
2

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/

  • 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 '14 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 '14 at 8:24
  • Thanks Shanky, actually I was looking for What is VLFs fragmentation. – AA.SC Jul 9 '14 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 '14 at 8:53
1

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)

1

I would not get hung up on number of VLFs, especially with 160 VLFs. If the VLFs are in 1000s 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 it's sequential. If it's been growing in very small chunks, then wait 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/

  • 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 '14 at 10:51
0

I would not worry about number of VLFs unless they are in 1000s as mentioned above as well. Some people call too many VLFs as fragmented VLFs especially if they are in very small chunk though it is not same as fragmentation in data file. Too many VLFs can slow down the backup and recovery process.

However VLFs size matter more than numbers I think. You can also check the size of VLFs which give you an idea if they are too small (fragmented) and then you can update DB autogrowth accordingly. There is change to VLF creation algorithm from SQL Server 2014. https://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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