VLF (Virtual Log File) – SQL Server Performance

Leave a comment (0) Go to comments

VLF, Virtual Log File are logical transaction log files which are internally managed by SQL Server. A physical database transaction logs files is divided into multiple virtual log files to manage the WIP transactions.

VLF is an internal mechanism for SQL Server to work with the transaction log (mostly for truncation). We should not have large number of VLFs as it can lengthen recovery time and can negatively affect performance of your database. Ideally any count of VLF less than 50 is considered as good number, anything beyond 50 might affect the performance.

Here’s how the log is divided into VLFs. Each "chunk" that is added, is divided into VLFs at the time the log growth (regardless of whether this is a manual or auto-grow addition) and it’s all dependant on the size that is ADDED not the size of the log itself. So, take a 10MB log that is extended to 50MB, here a 40MB chunk is being added. This 40MB chunk will be divided into 4 VLFs. Here’s the breakdown for chunksize:

chunks less than 64MB = 4 VLFs

chunks of 64MB and less than 1GB = 8 VLFs

chunks of 1GB and larger = 16 VLFs

And, what this translates into is that a transaction log of 64GB would have 16 VLFs of 4GB each. As a result, the transaction log could only clear at more than 4GB of log information AND that only when it’s completely inactive.


Note :

To have a more ideally sized VLF, consider creating the transaction log in 8GB chunks (8GB, then extend it to 16GB, then extend it to 24GB and so forth) so that the number (and size) of your VLFs is more reasonable (in this case 512MB).

Read more from Kimberly

Question : How to check Number of VLF is Current Database ?

Answer : DBCC LogInfo, a undocumented SQL statement can be used to check VLF count in current database.

Question : How to check Number of VLF for all databases?

Answer : DBCC LogInfo, can be used either with cursor or sp_MSforeachdb to check VLF count for every database on a server. Here is a small TSQL code, which can also be used to check VLF count for all databases in a given server.

CREATE TABLE #eachDB(

FileID INT

, FileSize BIGINT

, StartOffset BIGINT

, FSeqNo BIGINT

, [Status] BIGINT

, Parity BIGINT

, CreateLSN NUMERIC(38)

);

CREATE TABLE #summary(

Database_Name sysname

, VLF_count INT

, Log_File_count INT

);

EXEC sp_MSforeachdb N'Use [?];

Insert Into #eachDB

Exec sp_executeSQL N''DBCC LogInfo(?)'';

Insert Into #summary

Select DB_Name(), Count(*), Count(Distinct FileID)

From #eachDB;

Truncate Table #eachDB;'

SELECT *

FROM #summary

ORDER BY VLF_count DESC;

DROP TABLE #eachDB;

DROP TABLE #summary;

Question : If i have 5 VLF’s in a database. VLF Number 2 in use by transaction 2 and VLF number 3,4,5 in use by transaction 4. When transaction 4 finishes, which will be the next VLF sequence? number 1 (2.3..) or number 4 (5,1,2…) .assuming transaction 3 is still in progress.?

Answer : The next new transaction will start from the point where the last transaction has ended. In this case, Transaction 4 will continue to use the current end-most VLF (5 in this case). When 5 is full, next one would be 1.

Question : How to reduce the VLF count if this is in 100+?

Answer :

Step 1 == >  Check VLF Count using DBCC LogInfo

Step 2 ==>  Backup the Transaction Log

Step 3 == >  Shrink Transaction Log to least minimum size using TRUNCATEONLY option

Step 3 == >  Check VLF Count using DBCC LogInfo

Step 3 == >  Grow the log file back to the desired size, you might want to increase transaction log in 8GB batches, read start of article

Step 3 == >  Check VLF Count using DBCC LogInfo

EOF - VLF (Virtual Log File) – SQL Server Performance, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.