SQL Log File and Virtual Log File Size

When creating a database, its initial file size, file growth rate, and its auto growth options are some of the important settings need careful and detailed planning. The same hold true for its log file. This post mainly focus on the log file. What file size should be used?

Lets first try to understand the log file structure. When physical log file is first created (or grow in size), SQL Server database engine divide each physical log file internally into multiple virtual log files (VLFs). The calculation of how it is divided depends on the added size (or the newly created log size),

Added size less than 1MB = 2 VLFs
Added size larger than 1MB and up to 64MB = 4 VLFs
Added size larger than 64MB and up to 1GB = 8 VLFs
Added size larger than 1GB = 16 VLFs

For example, a newly created transaction log file of 8GB will have 16 VLFs with 500MB each. A log file with initial size of 10MB and with addition of 40MB grow in size (manual/auto-grow) will have the initial 4 VLFs and the newly added 4 VLFs, total of 8 VLFs. Setting initial size and auto-growth size need to be carefully evaluated and planned. If the initial size is too small and it is filled up quickly, it needs to expand. If the growth increment is too small, the log file will have to expand frequently to accommodate the need. That will lead to creation of large number of VLFs, which could cause performance problem during database startup, replication, mirroring and sometimes affect the performance of data modification. It is recommended to have the log file size value set close to the final size required, and have relatively larger growth increment. However, the growth increment should not be too big as well. The transaction log would take much longer time to reuse the VLF as the large VLF needs to become completely inactive before it could be cleared. It may affect performance during the truncation of this huge inactive VLF. In general, the number of VLFs should be 20-30, or reasonable with even 50 VLFs in the transaction log depending on the transaction log total size (See Kimberly Tripp's blog for more detail).

Update: Jan 7. 2014
The above formula is valid up to SQL Server 2014. SQL Server 2014 use different algorithm calculation for the VLF creation.

The new formula is,
If the added size (growth size) is less than 1/8 of the size of the log file, create 1 VLF equal the size of the added size.
If the added size (growth size) is equal or large than 1/8 of the size of the log file, use the previous calculation.

With the understanding how virtual log files are created and how it affects performance, next step is to determine the usage of the database to estimate the initial size and file growth rate for the log file. The size of the log need to be large enough to prevent frequent file grow (which may affect performance during the file grow), but just about enough to accommodate the usage without excessive space (resulting too large the size of VLF). Auto growth is enabled by default to allow the file to grow whenever is needed. If drive space is a concern, maximum size of the file can be set.

To monitor the current file size, growth rate, auto-growth and maximum size settings,

SELECT 
 name
 , type_desc
 , size * 8 / 1024 AS current_size_MB
 , CASE WHEN is_percent_growth = 0 
  THEN CAST(growth * 8 / 1024 AS varchar) + ' MB' 
  ELSE CAST(growth AS varchar) + '%' 
  END AS growth_rate
 , CASE WHEN (max_size = -1 OR max_size = 268435456)
  THEN 'UNLIMITED'
  ELSE CAST(max_size * 8 / 1024 AS varchar) + ' MB' 
  END AS max_size
FROM sys.master_files
WHERE DB_NAME(database_id) = 'YourDatabase';

The size column is current size of the file in 8-KB pages. The max size number 268435456 is the max size of 2 TB for log file. For my environment, I like to see the size in MB, hence the calculation. More details of sys.master_files table can be found here.

To look up log file size of all databases,

DBCC SQLPERF (LOGSPACE);

The results return four columns,

Database Name	Name of the database for the log statistics displayed.
Log Size (MB)	Current size allocated to the log. This value is always smaller than the amount originally allocated for log space because the Database Engine reserves a small amount of disk space for internal header information.
Log Space Used (%)	Percentage of the log file currently occupied with transaction log information.
Status	Status of the log file. Always 0.

To find out how many VLFs within the physical log file,

DBCC LOGINFO ('YourDatabase');

The results return the following columns,

FileID	Physical log file identifier from sysfiles
FileSize	Virtual log file size
StartOffset	Beginning point of the virtual log file
FSeqNo	Virtual log file's sequence number
Status	Whether the virtual file contains the active part of the log (logical log). 0 means that virtual file does not contain the active portion of the log; 2 means that it does
Parity	Parity information for virtual log file
CreateLSN	Log sequence number that began the virtual log file