Adding a New Transaction Log File when the Existing Log is Full

SQL Server Transaction Log files store details about every change made to a SQL Server Data File. This includes when you need to add a 2nd log file. The act of adding a 2nd log file changes data in the primary filegroup for the database. Since every change is logged, that change is logged too. So, what happens if you completely run out of log file space? Can you add a 2nd log file to avoid calamity? Turns out you can.

Not Adding Transaction Log Files

     What does this have to do with transaction log files?
           Not much; but that’s a pretty big log!

SQL Server 2008+ sets aside some space inside the most recently used virtual log file, or VLF, such that you are able to add another log file. The code below shows that in action.

First, we’ll create a database with a 10MB log file that we can fill up:

Next we’ll set the recovery model to full:

This creates a table, and adds enough data to fill up the log file:

Run this code several times until you see a message stating the log file is full:

The error will look like:

Msg 9002, Level 17, State 2, Line 21
The transaction log for database 'LogGrowthTest' is full due to 'LOG_BACKUP'.

Now, issue a checkpoint just for fun:

And, finally, add a new log file:

We can check which VLF has the details for the newly added log file with the undocumented table-valued-function, sys.fn_dblog. sys.fn_dblog returns a row for each active, uncleared record in the transaction log. With the database in simple recovery model, you’ll only see rows returned that haven’t been committed and subsequently cleared via log truncation. We set the recovery model to full when we created the test database so VLFs don’t get truncated.

Down towards the end of the output we see LOP_CREATE_FILE in the “Operation” column, while the “Current LSN” column provides the log sequence number. The LSN looks like:

00000028:00001385:000a

The numbers are in hexadecimal format. The first piece, 00000028, is the VLF File Sequence Number (FSeqNo). This number uniquely identifies the VLF where the action was recorded. The last two pieces aren’t important for this discussion; they consist of the log block, and the log record offset within that block. Anyway, if you look at the log with DBCC LOGINFO, you’ll see that VLF FSeqNo 40 (which is 0x28 in hex – the first piece of the LSN above) exists in FileID 2, which is the file ID from sys.database_files for the first log file. The output of DBCC LOGINFO for my test with the relevant row highlighted, looks like:

╔════════════════╦════════╦══════════╦═════════════╦════════╦════════╦════════╦═══════════╗
║ RecoveryUnitId ║ FileId ║ FileSize ║ StartOffset ║ FSeqNo ║ Status ║ Parity ║ CreateLSN ║
╠════════════════╬════════╬══════════╬═════════════╬════════╬════════╬════════╬═══════════╣
║              0 ║      2 ║  2555904 ║        8192 ║     37 ║      2 ║     64 ║         0 ║
║              0 ║      2 ║  2555904 ║     2564096 ║     38 ║      2 ║     64 ║         0 ║
║              0 ║      2 ║  2555904 ║     5120000 ║     39 ║      2 ║     64 ║         0 ║
║              0 ║      2 ║  2809856 ║     7675904 ║     40 ║      2 ║     64 ║         0 ║
║              0 ║      3 ║  2555904 ║        8192 ║      0 ║      0 ║      0 ║         0 ║
║              0 ║      3 ║  2555904 ║     2564096 ║      0 ║      0 ║      0 ║         0 ║
║              0 ║      3 ║  2555904 ║     5120000 ║      0 ║      0 ║      0 ║         0 ║
║              0 ║      3 ║  2809856 ║     7675904 ║      0 ║      0 ║      0 ║         0 ║
╚════════════════╩════════╩══════════╩═════════════╩════════╩════════╩════════╩═══════════╝

You can get the file_id, name, and physical name of the files with this query:

As you can see, file_id 2 is LOG, the initial log file, and file_id 3 is the newly added log file, LOG2.

╔═════════╦═════════╦════════════════════════════════════╗
║  name   ║ file_id ║           physical_name            ║
╠═════════╬═════════╬════════════════════════════════════╣
║ PRIMARY ║       1 ║ G:\Data\LogGrowthTest_primary.mdf  ║
║ LOG     ║       2 ║ F:\Data\LogGrowthTest_logfile.mdf  ║
║ LOG2    ║       3 ║ F:\Data\LogGrowthTest_Logfile2.mdf ║
╚═════════╩═════════╩════════════════════════════════════╝

It’s pretty great that Microsoft allows you to add a log file even when the existing log file is full, however I recommend pro-actively managing your log file.

This post is part of our series on Database Internals and Database Recovery.

See Microsoft’s Documentation for an overview of log sequence numbers.