Why a Second Log File Does Not Make Anything Faster

A second log file adds room to your transaction log, not speed. SQL Server fills one log file, then moves on to the next. So the extra file helps only when the first one is full.

Why people add a second log file

Picture a 2 AM page. The log drive is full and a big job has stopped. Someone adds a second log file on a drive with free space. The job finishes and everyone goes back to bed.

Then the story drifts. By morning the fix is called “we doubled the log.” A week later someone suggests a third file to make commits faster. That last idea is the one I want to talk you out of.

A second log file is a spare room, not a second lane on the highway. Let me build the emergency on a small demo database so you can watch.

Start with a log that has no room

The demo database gets an 8 MB log that cannot grow, like a full log drive. I use SIMPLE recovery so you need no backups. The first result shows one log file of 8 MB. The second shows four virtual log files, one of them active.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
ALTER DATABASE SqlAuthorityDemo SET RECOVERY SIMPLE;
ALTER DATABASE SqlAuthorityDemo MODIFY FILE (NAME = SqlAuthorityDemo_log, FILEGROWTH = 1MB);
GO
ALTER DATABASE SqlAuthorityDemo MODIFY FILE (NAME = SqlAuthorityDemo_log, MAXSIZE = 8MB);
GO
USE SqlAuthorityDemo;
GO
SELECT file_id, name, type_desc, size / 128 AS size_mb, max_size / 128 AS max_mb
FROM sys.database_files
WHERE type = 1;

SELECT file_id, vlf_sequence_number, vlf_active
FROM sys.dm_db_log_info(DB_ID())
ORDER BY file_id, vlf_begin_offset;

Now ask for more log than exists. One insert of 2000 rows, about 4 KB each, needs more than 8 MB. It fails with error 9002 and the table stays empty.

CREATE TABLE dbo.LogFiller (id int IDENTITY PRIMARY KEY, pad char(4000) NOT NULL);
GO
INSERT dbo.LogFiller (pad)
SELECT TOP (2000) 'x'
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;
GO
SELECT COUNT(*) AS rows_loaded FROM dbo.LogFiller;

The message says the log is full due to ACTIVE_TRANSACTION. The insert is the open transaction, so nothing can be reused while it runs. Nothing is broken. The log just has no room.

Add the second file and watch the order

Now add a second file with its own 32 MB. I made it big because SQL Server also keeps room to roll a statement back. The demo creates the SqlAuthorityDemo database and drops it at the end.

DECLARE @LogFolder nvarchar(300) =
    CONVERT(nvarchar(300), SERVERPROPERTY('InstanceDefaultLogPath'));
DECLARE @Sql nvarchar(max) = N'ALTER DATABASE SqlAuthorityDemo ADD LOG FILE
    (NAME = SqlAuthorityDemo_log2,
     FILENAME = N''' + @LogFolder + N'SqlAuthorityDemo_log2.ldf'',
     SIZE = 32MB, MAXSIZE = 32MB, FILEGROWTH = 1MB);';
EXEC (@Sql);
GO
BEGIN TRANSACTION;

INSERT dbo.LogFiller (pad)
SELECT TOP (2000) 'x'
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;

SELECT file_id, vlf_sequence_number, vlf_active
FROM sys.dm_db_log_info(DB_ID())
ORDER BY file_id, vlf_begin_offset;

COMMIT TRANSACTION;

Read the last result from top to bottom. All four virtual log files in the first file are active. The log then continues into the second file, where one is active. The sequence numbers keep counting up as the log steps across.

That is the whole story. SQL Server did not write to both files at once. It filled the first, then stepped into the second. A faster drive for the second file would not have sped up the first one.

Look for the real bottleneck first

Before you add a file, ask why the log filled up. Column log_reuse_wait_desc names what holds the log. NOTHING means space can be reused. ACTIVE_TRANSACTION or LOG_BACKUP point at the real cause. In the demo it says NOTHING because the insert has committed.

The second query shows writes and write stalls per log file. Run it before and after any change. Your numbers will differ from mine, so compare your own.

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = N'SqlAuthorityDemo';

SELECT file_id, num_of_writes, io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL)
WHERE file_id IN (SELECT file_id FROM sys.database_files WHERE type = 1)
ORDER BY file_id;

The usual suspects are a long open transaction, a missing log backup in FULL recovery, or a replica that has fallen behind. A second file fixes none of them. It only buys time.

Retire the extra file when the emergency is over

An emergency file needs an exit plan. Try to remove it now and SQL Server says error 5042, because the file is not empty. It still holds part of the active log.

The loop below keeps the log busy with small commits and checkpoints. It stops when no active virtual log file is left in the second file. The counter keeps it from running forever.

ALTER DATABASE SqlAuthorityDemo REMOVE FILE SqlAuthorityDemo_log2;
GO
DECLARE @Passes int = 0;
WHILE @Passes < 50
  AND EXISTS (SELECT 1 FROM sys.dm_db_log_info(DB_ID())
              WHERE file_id = FILE_ID(N'SqlAuthorityDemo_log2') AND vlf_active = 1)
BEGIN
    DELETE dbo.LogFiller;
    INSERT dbo.LogFiller (pad)
    SELECT TOP (1000) 'x' FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
    CHECKPOINT;
    SET @Passes += 1;
END;

SELECT file_id, vlf_sequence_number, vlf_active
FROM sys.dm_db_log_info(DB_ID())
ORDER BY file_id, vlf_begin_offset;

Now the active virtual log file sits in the first file, and the second file’s are all inactive. The last block removes the file for real and drops the demo database.

ALTER DATABASE SqlAuthorityDemo REMOVE FILE SqlAuthorityDemo_log2;

SELECT file_id, name, type_desc
FROM sys.database_files
WHERE type = 1;
GO
USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Never delete the file from Windows. SQL Server still expects it to exist.

Next time the log fills up, fix the reason first, then decide how much room to add.

A second log file is not a speed upgrade, it is a spare room.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.