VLF is SQL server internal logical structures of log files. Too many VLFs can cause major performance bottle neck. Excessive VLF can cause slow restores, excessive locking, affects HA (high availability) groups and reduces transaction log backup performance. Detecting and resolving the VLF issue is very simple by pre-sizing, using MB or GB interval for sizing.
Any transaction written to the log file has a start time and an end time, and during this written process if the log is backed up then the running transaction is not going to truncate. Once transaction is done, the next log backup will truncate all the committed transaction, and the space used by the VLF would be empty for reuse.
What happens if the transaction is bigger than the log file size? The bigger the transaction size the more VLFs would be created.
So think if a transaction size is 5 GB and your logfile size is set to 1 GB, it will create 64 VLF (4×16).
To see any excessive VLF and to remove them, follow the below steps:
Step 1: Run DBCC loginfo ()
Step 2: Take transaction log backup to truncate all the commented transactions.
Step 3: Shrink the log file DBCC shrinkfile (logfile logical name)
If you see the used pages and estimated pages has a huge gap between the numbers, it means active transactions exist.
where database_id = 2
(get the database_ID from select * from sys.sysdatabases where name =’your database’)
Then Run the DBCC loginfo () again to check the current status. If you see what is in the below picture, a log status of 2 means it is an active transaction. And you can not shrink the previous ones (status 0) because they have already been committed, at this point you might need to run transaction log backup again. And shrink the log file again.
-By : MD Ullah