As Robbie says, you never need more than one log file for any database. The only situation where you need to two create a second file is when you realise that the current log file is too small, and there is not space enough on the disk where it is located. Then you may have to resort to create a second file on a second disk. However, this would only be a temporary solution, until you can find the disk space for a single big log file.
Do we need to create additional tempfile in sqlserver 2016?
In 2012, since there was logfile growth so added additional logfiles, do we need to also add additional logfile in 2016?
Sign in to comment
2 additional answers
Sort by: Most helpful
Hi, there only needs to be one log file for tempdb as the file is circular.
As everyone here said, there is no need to set multiple files for the transaction log in SQL Server, only under special circumstances (for example, the current log file and the disk space where it is located have been used up and cannot be automatically increased). Because SQL Server writes log records in strict order, and only writes one file at the same time, adding multiple log files will not help performance. If a database has two log files, SQL Server still writes only one of them at a time. Only when this file is full, SQL Server will write another one.
This is different from data files. For data files. SQL Server will distribute the new data to all data files with space according to the size of the existing free space of all files in the same file group. Sometimes we will add multiple database files and put them on different hard disks to achieve the purpose of distributing the I/O load.