Friday, December 4, 2015

Basics of Virtual Log Files in SQL Server

The transaction log for a database is a set of VLFs whose size is determined by he total size of all the log files and the auto growth setting.
The VLF size varies from 2 to 16.

Below is the design

No. of VLF= size of log file/min. VLF size
1 - 64 MB          = 4 VLFs
>64 MB - 1 GB = 8 VLFs
>1GB                 = 16 VLFs

States of VLF

 Active
Any VLF which is the part of the active log.

 Recoverable
The portion of log preceding the oldest active transaction is needed to maintain a sequence of log backups for restoring the database to a former state.

 Reusable
If d transaction log is being backed up, VLFs before the oldest active transaction is not needed and can be reused.
Truncation or backup operation of a transaction log changes recoverable VLFs into reusable VLFs.

 Unused
One or more VLFs at the physical end of the log files might not have been used yet if not enough logged activity has taken place or if earlier VLFs have been marked as recoverable and then reused.



Command for VLF details
DBCC LOGINFO

Check the status column
0 => Reusable or Unused
2 => Active or Recoverable

This is a basic information of VLF in SQL Server.
Hope this helps you understand.
 

No comments:

Post a Comment