HOW TO SPEED UP SQL SERVER LOG FILE BY REDUCING NUMBER OF VIRTUAL LOG FILE

A log file records all the changes that occur in a SQL Server database. This log file contains multiple virtual log files(VLFs) these are used to manage space reuse. If SQL Server needs more space to store this information the log file will increase by its Autogrowth size. Increasing the size of the log file will create additional virtual log files. The Number of virtual log files added depends on filegrowth size.

For less than 64MB 4 VLFs are added, between 64mb and 1GB 8 VLFs are added and for more than 1GB 16 VLFs are added.
If there are a large amount of virtual log files database startups and restore and backup operations will be slowed down.
To reduce the number of virtual log files make sure the initial size is close to the final size required, and also have a large Autogrowth value.

You can see the number of virtual log files by using:

dbcc loginfo

Any number less than 50 VLFs is considered good.

If you already have too many virtual log files you can reduce them by shrinking the log file see:
Shrink a log file

If you want to make sure the number of virtual log files doesn’t become too big:

Steps:

1. Set the initial size.

USE master;  GO  ALTER DATABASE [Adventure Works 2008]  MODIFY FILE  (NAME = AdventureWorks2008_Log,  SIZE = 100MB);  GO

2. Set the autogrowth size

USE master;  GO  ALTER DATABASE [Adventure Works 2008]  MODIFY FILE  (NAME = AdventureWorks2008_Log,  Filegrowth = 20MB); GO