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.
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
>64 MB - 1 GB = 8 VLFs
>1GB = 16 VLFs
States of VLF
Active
Any VLF which is the part of the active log.
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.
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.
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.
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
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