VLF truncation in SIMPLE recovery model
I recently presented a session on log file management where I discussed monitoring and managing the logical segments of the transaction log known as Virtual Log Files, or VLFs. A great question came up about VLF truncation in simple recovery model. The question posited that once a VLF contains only committed transactions, it can be truncated, even if a prior uncommitted transaction still exists in an unrelated VLF. In response to that question, I’d like to present the details about when and how VLFs are truncated, or freed-for-reuse, in simple recovery model.
A few peasants probably not discussing VLF truncation in simple recovery model.
If VLFs are new to you, see my post discussing the structure of the transaction log for details about the layout of the transaction log, and how VLFs work.
The [tl/dr;]: VLF truncation occurs once all log records contained in the VLF have been committed to disk, and no prior VLFs exist in the log sequence containing uncommitted transactions.
To see VLF log truncation in action, it’s easiest to create a new, dedicated database with a small log file:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
USE master; IF DB_ID(N'vlf_test') IS NOT NULL BEGIN ALTER DATABASE vlf_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE vlf_test; END CREATE DATABASE vlf_test ON (NAME = N'vlf_test_primary' , FILENAME = N'G:\Database\vlf_test_primary.mdf' , SIZE = 10MB , FILEGROWTH = 10MB , MAXSIZE = 100MB) LOG ON (NAME = N'vlf_test_log' , FILENAME = N'F:\Database\vlf_test_log.ldf' , SIZE = 1MB , FILEGROWTH = 1MB , MAXSIZE = 100MB); ALTER DATABASE vlf_test SET RECOVERY SIMPLE; BACKUP DATABASE vlf_test TO DISK = N'NUL:'; GO |
Next, we’ll create a table in the database, and take an initial look at the VLFs in the transaction log:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
USE vlf_test; CREATE TABLE dbo.some_data ( i int NOT NULL CONSTRAINT some_data_pk PRIMARY KEY CLUSTERED IDENTITY(1,1) , j varchar(max) NULL ) ON [PRIMARY]; CHECKPOINT; SELECT [begin_offset] = li.vlf_begin_offset , [size] = li.vlf_size_mb , [sequence] = li.vlf_sequence_number , [active] = li.vlf_active FROM sys.dm_db_log_info(DB_ID()) li ORDER BY li.vlf_begin_offset; |
The VLFs at this point:
╔══════════════╦══════╦══════════╦════════╗ ║ begin_offset ║ size ║ sequence ║ active ║ ╠══════════════╬══════╬══════════╬════════╣ ║ 8192 ║ 0.24 ║ 37 ║ 1 ║ ║ 262144 ║ 0.24 ║ 0 ║ 0 ║ ║ 516096 ║ 0.24 ║ 0 ║ 0 ║ ║ 770048 ║ 0.26 ║ 0 ║ 0 ║ ╚══════════════╩══════╩══════════╩════════╝
As you can see, the first VLF is marked active, with the rest of the 4 initially-created VLFs marked inactive.
For this next bit of code, we need to open a new query window, to run the code in a separate session.
|
1 2 3 4 |
BEGIN TRANSACTION; INSERT INTO dbo.some_data (j) SELECT TOP (10) (REPLICATE(CONVERT(varchar(max), 'A'), 60000)) FROM sys.syscolumns sc1; |
The code starts a transaction, then inserts 10 rows into our table. Now, we switch back to our original session, and take a look at the VLFs:
|
1 2 3 4 5 6 7 |
SELECT [begin_offset] = li.vlf_begin_offset , [size] = li.vlf_size_mb , [sequence] = li.vlf_sequence_number , [active] = li.vlf_active FROM sys.dm_db_log_info(DB_ID()) li ORDER BY li.vlf_begin_offset; |
╔══════════════╦══════╦══════════╦════════╗ ║ begin_offset ║ size ║ sequence ║ active ║ ╠══════════════╬══════╬══════════╬════════╣ ║ 8192 ║ 0.24 ║ 37 ║ 1 ║ ║ 262144 ║ 0.24 ║ 38 ║ 1 ║ ║ 516096 ║ 0.24 ║ 39 ║ 1 ║ ║ 770048 ║ 0.26 ║ 40 ║ 1 ║ ║ 1048576 ║ 0.24 ║ 0 ║ 0 ║ ║ 1302528 ║ 0.24 ║ 0 ║ 0 ║ ║ 1556480 ║ 0.24 ║ 0 ║ 0 ║ ║ 1810432 ║ 0.27 ║ 0 ║ 0 ║ ╚══════════════╩══════╩══════════╩════════╝
The next three VLFs are now marked active, and the log has grown to accommodate any new transactions that might occur.
Lets insert another 20 rows in the main transaction-less session, and check the log again:
|
1 2 3 4 5 6 7 8 9 10 11 |
INSERT INTO dbo.some_data (j) SELECT TOP (20) (REPLICATE(CONVERT(varchar(max), 'A'), 60000)) FROM sys.syscolumns sc1; SELECT [begin_offset] = li.vlf_begin_offset , [size] = li.vlf_size_mb , [sequence] = li.vlf_sequence_number , [active] = li.vlf_active FROM sys.dm_db_log_info(DB_ID()) li ORDER BY li.vlf_begin_offset; |
As expected, more VLFs have been marked active:
╔══════════════╦══════╦══════════╦════════╗ ║ begin_offset ║ size ║ sequence ║ active ║ ╠══════════════╬══════╬══════════╬════════╣ ║ 8192 ║ 0.24 ║ 37 ║ 1 ║ ║ 262144 ║ 0.24 ║ 38 ║ 1 ║ ║ 516096 ║ 0.24 ║ 39 ║ 1 ║ ║ 770048 ║ 0.26 ║ 40 ║ 1 ║ ║ 1048576 ║ 0.24 ║ 41 ║ 1 ║ ║ 1302528 ║ 0.24 ║ 42 ║ 1 ║ ║ 1556480 ║ 0.24 ║ 43 ║ 1 ║ ║ 1810432 ║ 0.27 ║ 44 ║ 1 ║ ║ 2097152 ║ 0.24 ║ 45 ║ 1 ║ ║ 2351104 ║ 0.24 ║ 0 ║ 0 ║ ║ 2605056 ║ 0.24 ║ 0 ║ 0 ║ ║ 2859008 ║ 0.27 ║ 0 ║ 0 ║ ╚══════════════╩══════╩══════════╩════════╝
However, since we’re in simple recovery model, and all those inserts we just did have been committed, the VLFs for those committed inserts should be available for truncation. What gives? Well, the fact that we have an uncommitted transaction in the first VLF, sequence 37, means SQL Server cannot truncate later log records. The behavior is the same, even if we issue a CHECKPOINT command, which truncates eligible VLFs:
|
1 2 3 4 5 6 7 8 9 |
CHECKPOINT; SELECT [begin_offset] = li.vlf_begin_offset , [size] = li.vlf_size_mb , [sequence] = li.vlf_sequence_number , [active] = li.vlf_active FROM sys.dm_db_log_info(DB_ID()) li ORDER BY li.vlf_begin_offset; |
╔══════════════╦══════╦══════════╦════════╗ ║ begin_offset ║ size ║ sequence ║ active ║ ╠══════════════╬══════╬══════════╬════════╣ ║ 8192 ║ 0.24 ║ 37 ║ 1 ║ ║ 262144 ║ 0.24 ║ 38 ║ 1 ║ ║ 516096 ║ 0.24 ║ 39 ║ 1 ║ ║ 770048 ║ 0.26 ║ 40 ║ 1 ║ ║ 1048576 ║ 0.24 ║ 41 ║ 1 ║ ║ 1302528 ║ 0.24 ║ 42 ║ 1 ║ ║ 1556480 ║ 0.24 ║ 43 ║ 1 ║ ║ 1810432 ║ 0.27 ║ 44 ║ 1 ║ ║ 2097152 ║ 0.24 ║ 45 ║ 1 ║ ║ 2351104 ║ 0.24 ║ 0 ║ 0 ║ ║ 2605056 ║ 0.24 ║ 0 ║ 0 ║ ║ 2859008 ║ 0.27 ║ 0 ║ 0 ║ ╚══════════════╩══════╩══════════╩════════╝
Let’s rollback the uncommitted transaction, from the 2nd session:
|
1 |
ROLLBACK TRANSACTION; |
Now, when we look at the VLFs, they’ve all been truncated except the very last VLF, which remains active and ready for new transactions:
|
1 2 3 4 5 6 7 8 9 |
CHECKPOINT; SELECT [begin_offset] = li.vlf_begin_offset , [size] = li.vlf_size_mb , [sequence] = li.vlf_sequence_number , [active] = li.vlf_active FROM sys.dm_db_log_info(DB_ID()) li ORDER BY li.vlf_begin_offset; |
╔══════════════╦══════╦══════════╦════════╗ ║ begin_offset ║ size ║ sequence ║ active ║ ╠══════════════╬══════╬══════════╬════════╣ ║ 8192 ║ 0.24 ║ 37 ║ 0 ║ ║ 262144 ║ 0.24 ║ 38 ║ 0 ║ ║ 516096 ║ 0.24 ║ 39 ║ 0 ║ ║ 770048 ║ 0.26 ║ 40 ║ 0 ║ ║ 1048576 ║ 0.24 ║ 41 ║ 0 ║ ║ 1302528 ║ 0.24 ║ 42 ║ 0 ║ ║ 1556480 ║ 0.24 ║ 43 ║ 0 ║ ║ 1810432 ║ 0.27 ║ 44 ║ 0 ║ ║ 2097152 ║ 0.24 ║ 45 ║ 1 ║ ║ 2351104 ║ 0.24 ║ 0 ║ 0 ║ ║ 2605056 ║ 0.24 ║ 0 ║ 0 ║ ║ 2859008 ║ 0.27 ║ 0 ║ 0 ║ ╚══════════════╩══════╩══════════╩════════╝
Summary
Virtual Log Files cannot be re-used, even under simple recovery model, until all transactions contained in prior virtual log files have been either committed or rolled-back. This applies even if a manual CHECKPOINT command is issued. Long running transactions cause recovery duration to increase since all activity recorded in the log file, even committed transactions, must be rolled forward in any and all active VLFs.
Read our series on recovery.