Every SQL Server Databases consists of two or more files, of which two types of files are mandatory, one of which is a data file and another transaction log file. Data file contains actual data in specific format which SQL Server can understand and transaction log files contains all the changes that are made to the data in the databases using DDL(Create/Alter/Drop) or DML(Insert/Update/Delete) queries. The transaction log is used to guarantee the data integrity of the database and for data recovery. Data files and transaction log files architecture is different and it is important as a DBA to understand the architectures of the data and log files in a database. In this article we will discuss about the VLFs in transaction log file and how to reduce those VLFs.
The transaction log in a database maps over one or more physical files. Conceptually, the log file is a string of log records. Physically, the sequence of log records is stored efficiently in the set of physical files that implement the transaction log. The SQL Server Database Engine divides each physical log file internally into a number of virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. The Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. The Database Engine tries to maintain a small number of virtual files. The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. The size or number of virtual log files cannot be configured or set by administrators.
At times the number of Virtual Log files (VLFs) are increased to very high number due to improper configuration to transaction log files. SQL Server follows an algorithm which creates these virtual log files based on the initial size and auto growth that occurs. For example, If the initial size of the log file is set to any value less than 64 MB, then there will be 4 VLFs created by SQL Server, if initial size is between 64MB and up to 1GB then 8 VLFs gets created and it initial size is greater than 1 GB then the number of VLFs created are 16. The same formula applies to auto-growth events of the log file too. Auto-growth evert occurs when the log file reaches the max initial size and all the space used is active, then physical log file size is increased on the auto-growth settings of the database.
If the initial size of database is set to very low value, then it will cause auto-growth and if this auto-growth values are set to low then there will be frequent small increase to the log file which can create lot of VLFs. If there are too many VLFs in a database log file, then it will cause performance issues and also affect certain operations which are dependent on log file. Some problems which occur due to large transaction log files include long recovery of database during startup which interrupts users from using the database, slow performance with replication, database mirroring, AlwaysON, etc. So, as a DBA it is important to maintain the number of VLFs to smaller size thus avoiding unnecessary problems.
When you notice that the number of VLFs are very high like more than 300 then you can follow below steps to reduce the number of VLFs
– First monitor for few days or month to see what is the maximum size the transaction log file of a particular database can grow when there are regular log backups performed. That will be the size the log file may grow in future too.
– First shrink the transaction log file to the smallest size possible(better perform this during off business hours as it will cause some performance problems). You may need to perform multiple log backups and perform shrink operation on log file multiple times to reduce the log file size to minimum.
– Once the log file is very low, now change the initial size of the log file to higher value like 2000 MB or 4000 MB or 8000 MB based on the estimated size the log file can grow to in future. For example, if you estimate that the log file may grow up to 20 GB, then set the initial size of the log file to 4000 MB first time and to 8000 MB second time and to 12000 MB third time and to 16000 MB fourth time and finally to 20000 MB.
ALTER DATABASE [DBName] MODIFY FILE ( NAME = N’LogicalLogFileName’, SIZE = 4000MB )
ALTER DATABASE [DBName] MODIFY FILE ( NAME = N’LogicalLogFileName’, SIZE = 8000MB )
ALTER DATABASE [DBName] MODIFY FILE ( NAME = N’LogicalLogFileName’, SIZE = 12000MB )
ALTER DATABASE [DBName] MODIFY FILE ( NAME = N’LogicalLogFileName’, SIZE = 16000MB )
ALTER DATABASE [DBName] MODIFY FILE ( NAME = N’LogicalLogFileName’, SIZE = 20000MB )
– Now set the auto-growth setting of the database log file to grow in increments of 1024MB.
Now the number of VLFs in the database will be less and will not increase by too much in future too.
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.