Mistake: adding a second transaction-log file to fix slow writes. Log records are written sequentially, so the additional file provides no performance benefit. section 9.4
The operational consequences are straightforward:
- SQL Server uses the second log file only after the first becomes full; it does not alternate writes between files. section 9.4
- Multiple log files do not add performance penalties during normal operation, but they can lengthen restores because each file may need to be zeroed. section 9.4
- Use a second log file only as a temporary emergency measure when the existing log has consumed a volume’s available space. section 9.4
The MarketingArchive ETL mistake
During a nightly parallel ETL run, MarketingArchive shows WRITELOG waits. Adding another log file seems reasonable, but it cannot relieve sequential log-writing pressure. A second file is justified only if the current volume is full and operations must continue temporarily. section 9.4
