VLF misconceptions and what you need to know

• Are used for rewinding transactions and keeping a track of the transactions
• VLF are created depending on the number of active transactions combined with transaction file growth
  Ex. If the file growth was less than 64mb there will definitely be 4 VLF files in the log
• There will always be at lest one record with Status = 2 ; which will signify the active transaction on the VLF 
• When there are inactive transactions and if a Checkpoint is issued the older VLFs are reused 
• The parity column will help to identify if the log has been reused and the possible number of times the log was reused ( it will start from 64 and increase 64 every time it’s been reused)
• Sometimes you would find VLF’s without any usage (where FSeqNo , Status and Parity = 0) , these VLF are added by the subsystem in case a roll forward or roll back is required( this is due to the many number of active transactions in the log)
• Clearing the log does not mean that the VLF’s would reduce but it would reduce to hold the active part of the transactions.

·         The attached script help understand how the VLF works and how the log is cleared for a database in SIMPLE RECOVERY. For a database  in FULL or BULK logged mode the script will have to be modified to accommodate the transaction log backup.
a
      https://skydrive.live.com/?cid=96894afe46b1a82b#!/edit.aspx?cid=96894AFE46B1A82B&resid=96894AFE46B1A82B!149&nd=1