High VLF Count in Database Log File

High VLF count can create a performance problem in huge OLTP SQL Server system. Every 10 GB of T-Log file, VLF count should not more than 50 VLFs.

For example, if Database transaction log file size is 50 GB then as per recommendation, lets calculate optimum value for this much size of log file...

First convert the total size multiple of 10...

Like Total Log file size = n*10

50 GB = 5 * 10 GB

So there n = 5

So in the care, number of recommended VLF count would be  5 * 50 VLFs = 250 VLFs

So in 50 GB T-log file size, VLF count should be below 250.

This specification is generic, so we can calculate to estimated number of VLF that should be in T-log file.

If our DB transaction log VLF's count are more than 1000 then we need to worry about it. Oh! come-on guys, worry is not good for DBA. So lets talk about solution...

Below are the steps which we can take to over come this high VLF issue....

1. Note down the current T-Log file size.

2. If the DB is in Full recovery mode, take T-Log backup and run checkpoint for the DB.

3. Shrink the DB-Log file to 2 MB.

4. If Database transaction log file auto growth is set very low (like 2 digit length in MB) set it appropriate. In normal cases, I recommend it to put it 10% log file auto-growth.

5. Now modify T-log file size to earlier noted file size (In step 1 we have noted).

In my-case, DB transaction log file size was 1.6 GB but the number of VLFs count in it was 6152. Haha ha.

I have taken t-log backup.

Set t-log file size to 2 MB.

Set t-log file size as it was earlier. 

Now its time to check VLF count and transaction-log file size..