Transaction Log Physical Architecture
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. There must be at least one log file for each database.
The SQL Server Database Engine divides each physical log file internally into a number of virtual log files (VLFs). 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.
sys.dm_db_log_info
Returns Virtual log file information of the transaction log. Note all transaction log files are combined in the table output. Each row in the output represents a VLF in the transaction log and provides information relevant to that VLF in the log.
dm_db_log_info dynamic management function replaces the DBCC LOGINFO statement.
Note
Virtual log file (VLF) creation follows this method:
- If the next growth is less than 1/8 of current log physical size, then create 1 VLF that covers the growth size (Starting with SQL Server 2014 (12.x))
- If the next growth is more than 1/8 of the current log size, then use the pre-2014 method:
- If growth is less than 64MB, create 4 VLFs that cover the growth size (e.g. for 1 MB growth, create four 256KB VLFs)
- If growth is from 64MB up to 1GB, create 8 VLFs that cover the growth size (e.g. for 512MB growth, create eight 64MB VLFs)
- If growth is larger than 1GB, create 16 VLFs that cover the growth size (e.g. for 8 GB growth, create sixteen 512MB VLFs)
The following query determines the databases with more than 100 VLFs in the log files, which can affect the database startup, restore, and recovery time.
SELECT [name], COUNT(l.database_id) AS 'vlf_count' FROM sys.databases s CROSS APPLY sys.dm_db_log_info(s.database_id) l GROUP BY [name] HAVING COUNT(l.database_id) > 100
Data is fascinating.
I make SQL Server faster and more reliable. Database administration isn’t about passing a certified exam, or about pointing and clicking your way through a crisis. Database administration is about applying the right solution at the right time, avoiding risk, and making robust choices that get you home each night in time for dinner with your family .
My areas of specialty are:
• SQL Server 2019-2008 performance tuning.
• Designing high availability and disaster recovery solutions.
• Securing SQL Server
• SSIS , SSAS
• Designing Data warehouse