Do we need to create additional tempfile in sqlserver 2016?

Avyayah 1,146 Reputation points
2021-06-29T18:56:48.513+00:00

In 2012, since there was logfile growth so added additional logfiles, do we need to also add additional logfile in 2016?
110329-tempfile2012.jpg

110330-tempfile2016.jpg

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,177 questions
Accepted answer
  1. Erland Sommarskog 72,971 Reputation points MVP
    2021-06-29T20:49:17.103+00:00

    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.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Robbie Varn 261 Reputation points
    2021-06-29T19:08:36.66+00:00

    Hi, there only needs to be one log file for tempdb as the file is circular.

    2 people found this answer helpful.
  2. Cris Zhan-MSFT 6,566 Reputation points
    2021-06-30T03:00:45.563+00:00

    Hi,

    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.