Are your SQL server log growth parameters set correctly?

We all know that SQL server log files are used to record transaction logs. However, the SQL Server database engine divides each physical log file into multiple virtual log files for management. In addition, the number of backlogs affects the database performance.

 

The virtual log file does not have a fixed size, and the number of virtual log files contained in the physical log file is not fixed. The Database Engine dynamically selects the size of virtual log files when creating or expanding log files.

 

The Database Engine tries to maintain a small number of virtual files. The virtual log file will affect system performance only when the log file is defined with a small size and growth_increment value. If these log files grow to a large volume due to many small increments, they will have many virtual log files. This reduces the speed of database startup, log backup, and restoration operations.

 

Is there less virtual files, the better? No.

 

We know that the transaction log is a type of rewound file. If the size of each virtual log is 8 GB, it will be cleared only when all the 8 GB files become inactive, in this way, SQL Server needs to clear 8 GB logs at a time, which also affects the performance.

 

Therefore, the virtual log size must be maintained at a reasonable level. Here, let's take a look at the growth of virtual logs:

 

Log Growth Value64 MB = 4 vlfs

Log growth is greater64 MBLess1 GB = 8 vlfs

Logs greater1 GB = 16 vlfs

 

Kimberly suggests that the system virtual size file with a large log file is 512 MB, that is, the initial log file size is 4 GB (16 VLF files ), then it increases at a speed of 4 GB each time, which can improve the performance.

 

You can use DBCC loginfo to view the number of VLF instances:

 

Here I create the allentest database. The LDF file size is 24 mb. We can see that it is initially 4 VLF files and the active VLF file 1 (status = 2 ). Create a table and execute the following statement to increase the log. You can see that the VLF has become two active:

 

Create TableTest(NameVarchar(20))

Insert IntoTestValues('Allentest')

Go100000

 

Back up the log file and view the VLF status:

Backup LogAllentestToDisk='D: \ MSSQL \ allentest. trn'

 

We can see that the first VLF file has become available, but the second VLF file continues to be used because it is not fully written.

 

For more information, see:Physical architecture of transaction logs