I am new to SQL SERVER, could anyone please explain that why do we have VLFs' in T-LOG and why not have 1 T-log itself. I am aware that VLFs' are logical but my question is why VLFs'? what is the benefit that we get from having VLFs'.
migrated from meta.dba.stackexchange.com Feb 4 at 21:10
This question came from our discussion, support, and feature requests site for database professionals who wish to improve their database skills and learn from others in the community.
SQL Server uses VLF to fragment the log file. This fragmentation of the log file makes log management easier for the engine, as it adds granularity to the file.
As some of the links that Aaron Bertrand has posted in the comment explain (I would like to add this one), SQL Server writes sequentially to the log file. It starts with the first VLF and, once it is full, continues with the next one and so on. Once all VLFs have been used, it starts over again from the first one. This mechanism requires the log file to free up space that is not being used anymore.
The log file keeps all active transactions in SIMPLE recovery model, and all transactions since the last log backup in FULL (or BULK_LOGGED) recovery model. Once a CHECKPOINT or a log backup happens, the transactions that have been either commited or rolled back are no longer active transactions, and the space in the log file they were using can be reused (log truncation).
SQL server will just truncate VLFs that do not contain any active transaction. It won't free up space in VLFs that contain any active transaction. This way, SQL can just keep track of which VLFs contain active transactions or not, and just make the ones without active transactions available for reuse. I guess -this should be better answered by Microsoft's developers and engineers- that this is far easier than having to check a full logical file, that could be physically fragmented, and having to know where exactly is the lower limit of the log in the file (MinLSN).
The number of VLFs in our log file is important. If we have too few, it is very unlikely that log truncation can happen, because VLFs will be so big that they usually will "host" an active transaction. Too may can lead us to log fragmentation. You can read this excellent blog post by Kimberly Tripp on the topic.