patternsqlMinor
Virtual Log Files (VLFs) Fragmentation
Viewed 0 times
fragmentationlogvlfsfilesvirtual
Problem
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?
Another thing that I want to confirm: Are the following 3 statements always true for newly added chunks in SQL server Virtual Log files?
- chunks less than 64MB and up to 64MB = 4 VLFs
- chunks larger than 64MB and up to 1GB = 8 VLFs
- chunks larger than 1GB = 16 VLFs
Solution
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/
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/
Context
StackExchange Database Administrators Q#69091, answer score: 2
Revisions (0)
No revisions yet.