Why we want to add second log file?
It is a process that we usually perform when there is no free space in the disk where the log file exists.
By creating a second log file on another disk, we allow the database’s log operations to continue on new disk.
You can find detailed information about the log file in the article “What is SQL Server Transaction Log“.
For a clearer understanding of the subject, let me explain logic of writing data to the log file.
Is auto log growth of the current Transaction Log file enabled?
If so, is there enough disk space on the log file, or does the log file has reached its maximum size (maximum transaction log file size is 2TB)?
If SQL Server can write to the first log file, it always writes to the first log file, but when it is not possible to write to the first log file, it writes to the other log file.
In other words, if you create 5 log files in one database, SQL Server will not be able to write logs to 5 different disks at the same time.
Therefore, increasing the number of log files do not increase the performance.
If you have more than one log file, you can enable auto growth of the log file that you want the database to use and then disable auto growth of the other log files.
You can do the same for data files.
You can find the necessary details in the article “How To Add Another Disk For a Database Without Interruption On SQL Server” when there is no free space on the disks.
Create a second log file in the database:
Right click on the database and click properties as below.
Then go to the Files tab and click Add on the bottom right corner of the screen.
As you can see on the screen below, we will create a new Logical Name for the new transaction log file. I named it as TestLog.
In the File Type section, we choose the LOG option to indicate that this new file will be the Log file.
When we select the Log option, Filegroup changes to Not Applicable. Because the Log file does not have a file group.
To determine the starting size of the file we will create, we write the value on Initial Size.
In Autogrowth / Maxsize, we determine the auto growth value of the log file we will create.
You must be very careful when determining the autogrowth value of the log file for the performance of the database.
You should read the below article for understanding performance affect of the autogrowth value of the log file.
Click ok afte all our operations have completed.
If you want the database to use only the second log file instead of the first log file, you can disable the autogrowth of the first log file.
To do this, click “…” next to Autogrowth / Maxsize in the first log file and remove selection on “Enable Autogrowth”.