The transaction log files, records all the changes made to the SQL Server database as well as keeps the transactions. It is known to be the inevitable part of the SQL Server. These files are again divided into smaller virtual log files. If the number of virtual log files is more then, it is clear that the performance of transaction logs will get slow down. Here, in this session you will get to know about it in detail. Anyways, before moving into the effects, let’s have a check on what is ‘Virtual Log’ file.
Virtual Log File
Each smallest part of transaction log file is virtual log file. In other words, we can say that transaction log file is made up with the support of virtual log file. There is no limit kept regarding the number of virtual log files in transaction log files. It is the duty of SQL Server to determine the number, always it is tried to maintain a small amount of virtual log files. One thing to be pointed is that, the size cannot be set by administrator.
By default, transaction log file size is 2MB and the growth value is 10 percent. One can modify the option at the time of creating a Server database. There is an auto-growth choice which will be on by default and is optional too. The growth of the file can be in measured in MB or percent and is restricted but, initially the Server creates database of unrestricted growth. However, if settings of auto-growth are not maintained, database itself will force the growth option and leads to issues. You won’t see any rules set for the auto-growth, it varies. The point here is that, too and/or less virtual files are bad for the performance.
How does the increase in the number of virtual log file affect the performance badly? Let us find out how it is from the coming sessions.
Too Many Virtual Log Files Are Bad
Transaction logs are composed of physical files and internal to these files there is a structure called virtual log files. The Server initially writes to this physical file and there can be one or more physical log files. It is mandatory that, transaction log files should take the appropriate size. For SAP systems, a rough calculation made is;
- Small: 20-30 GB
- Medium: 100-200 GB
- Large: approximately 500 GB
- If, very large: approximately 1 TB
If the size of the virtual log files increases then, performance of the Server will be affected. There may be many factors that contribute to the increase of the size of the VLFs. If the number of these files in the transaction log files is large, it will affect the process of recovery badly; process slows down. The actual threshold for the recovery is around 10,000 virtual log files. It is noted that, when the number of the VL files is above 100,000, the side effect or symptoms appears.
How does the number of virtual log files increases in SQL Server?
The number of the virtual log files can be increased because of two reasons. It can be because of either the initial size of the log have increased automatically or by the mistake of human or can be because of the growth of the transaction log which leads to the growth of the virtual log files that are inside the transaction file. This is a new situation that was caught in all the Server products.
How to correct it?
To correct this situation follow:
- Take a backup of the transaction logs
- Decrease the transaction log using DBCC SHRINKFILE (you will have to do the execution multiple times).
- Swell the transaction log back to its initial size.
- Set growth size to be in 1 GB.
You should see that, you don’t grow database log files even in small amount since it leads to the numbering of virtual log files. Suppose if you found that there is large number of virtual log files, you should follow worthy steps to reduce it like, shrinking the file.
Virtual log files are the part of transaction logs, you cannot avoid it. The growth of the file can be done with the auto-growth settings, but, should be done carefully. If the size is increased, it may lead to decrease in the performance. The only option to decrease is shrinking the transaction files. You can also check the SQL Server virtual log files performance via powershell.