In my earlier blog post on Shrinking the Transaction Log files in SQL Server, I had mentioned that a VLF can be truncated only when all transactions, that have at least one command written into the specified VLF have either been committed or rolled back.

While this is true, I understand that it might create a bit of confusion around how the truncation actually happens behind the covers. In his comment, David writes:
Also, I have read in the documentation (Transaction Log Physical Architecture) that truncation of the log marks any VLFs as inactive "whose [log] records all appear in front of the ... MinLSN".  Shouldn't that continue on and say "or after the last-written log record"?  Or does "in front of" include the tail-end VLFs?  Please excuse me, I am having a hard time "wrapping my mind around" the logical log.

This post is essentially to help David, and many more of my readers understand the logic and the under-the-covers steps that SQL Server does while truncating a Transaction Log File. Hence, I will try and demonstrate, with examples, a Log File Truncation.

For demonstration purposes, let us create a new database, and make sure that the database is in FULL recovery model. Why Full Recovery Model – because in Full Recovery Model, truncation will happen only after we take a log backup, and hence, will be completely under our control. Lets first create a blank new database using the script below:

CREATE DATABASE TLogTruncationDemo
ON PRIMARY (
    NAME = 'TLogTruncationDemo_Data',
    FILENAME = 'F:\TLogTruncationDemo\TLogTruncationDemo_Data.mdf',
    SIZE = 10 MB,
    MAXSIZE = 100 MB )
LOG ON (
    NAME = 'TLogTruncationDemo_Log',
    FILENAME = 'F:\TLogTruncationDemo\TLogTruncationDemo_Log.ldf',
    SIZE = 2 MB,
    /* Set the MAXSIZE = SIZE so that the T-Log does not grow. */
    MAXSIZE = 2 MB )
GO

Now, before we proceed any further, let us change the Recovery Model of the database to Full, and then lets take a Full Backup, so that the Recovery Model change takes effect.

ALTER DATABASE TLogTruncationDemo SET RECOVERY FULL
GO
BACKUP DATABASE TLogTruncationDemo 
    TO DISK = 'F:\TLogTruncationDemo\TLogTruncationDemo_Backup_01.bak'
GO

DBCC LOGINFO(TLogTruncationDemo)
GO

Now, execute the following command:

DBCC LOG(TLogTruncationDemo)
GO

CREATE TABLE TblTLogDEMO (
    C1 INT IDENTITY(1, 1) NOT NULL,
    C2 INT NOT NULL,
    C3 VARCHAR(10) NOT NULL )
GO
INSERT INTO TblTLogDEMO (C2, C3) VALUES (1, 'A')
INSERT INTO TblTLogDEMO (C2, C3) VALUES (2, 'A')
INSERT INTO TblTLogDEMO (C2, C3) VALUES (3, 'A')
GO
CHECKPOINT
GO

Current LSN             Operation       Context   Transaction ID
----------------------- --------------- --------- --------------
00000014:0000006e:009e  LOP_BEGIN_CKPT  LCX_NULL  0000:00000000
00000014:000000af:0001  LOP_END_CKPT    LCX_NULL  0000:00000000

00000014:0000006e:009e is the LSN when the CHECKPOINT started and it ended at LSN 00000014:000000af:0001. Behind the covers, at LSN 00000014:000000af:0001, i.e., at LOP_END_CKPT, SQL Server also records another LSN, which is called the Minimum Recovery LSN (MinLSN).

The Minimum Recovery LSN (MinLSN) is the LSN of the first log record that must be present for a successful database-wide rollback. The MinLSN is the minimum of the:

• LSN of the start of the checkpoint.
• LSN of the start of the oldest active transaction.
• LSN of the start of the oldest replication transaction that has not yet been delivered to the distribution database.

At this point, since we do not have replication configured, and we do not have any active transaction either, the MinLSN will be equal to the LSN of the start of the CHECKPOINT process, i.e., 00000014:0000006e:009e.

EXPERIMENT 1 :: COMMIT, CHECKPOINT, BACKUP LOG

We will perform the following activities:

BEGIN TRAN Demo_Tran_01
GO
INSERT INTO TblTLogDEMO (C2, C3) VALUES (1, 'A')
GO 3050
COMMIT TRAN Demo_Tran_01
GO
CHECKPOINT
GO

Current LSN             Operation       Context   Transaction ID
----------------------- --------------- --------- --------------
00000015:000000eb:0007  LOP_BEGIN_CKPT  LCX_NULL  0000:00000000
00000015:000000ee:0001  LOP_END_CKPT    LCX_NULL  0000:00000000

At this point, we can clearly understand that at this stage, all the LSNs in VLF 1 are less than the MinLSN, while VLF 2 has at least 2 LSN that are either equal to or greater than the MinLSN.

When a Transaction Log File is truncated, all entries having “Current LSN” less than the MinLSN are deleted. Later, any VLF, that becomes completely empty is marked as Inactive. In other words, VLFs that consist only of LSNs less than the MinLSN are marked as Inactive and Truncated.

In our case, VLF 1 consists only of LSNs less than the MinLSN, and hence only VLF 1 should be truncated. To verify this, lets take a Log Backup of the database using the command below:

BACKUP LOG TLogTruncationDemo
    TO DISK = 'F:\TLogTruncationDemo\TLogTruncationDemo_LogBackup_01.trn'
GO

EXPERIMENT 2 :: CHECKPOINT, BACKUP LOG, COMMIT

In the second part of the exercise, let us begin a transaction and then continue inserting records, so that the commands completely fill up VLF 2 and moves on into VLF 3. Later, we will fire a manual CHECKPOINT. We use the script below:

BEGIN TRAN Demo_Tran_02
GO
INSERT INTO TblTLogDEMO (C2, C3) VALUES (1, 'A')
GO 3000

CHECKPOINT
GO

Now, when we fire the CHECKPOINT, there is an active transaction, that started at LSN 00000015:000000ef:0002 (LOP_BEGIN_XACT), while the LSN corresponding to the last LOP_BEGIN_CKPT is 00000016:00000100:00d7. Since, 00000015:000000ef:0002 (LOP_BEGIN_XACT) is less than 00000016:00000100:00d7 (LOP_BEGIN_CKPT), the MinLSN will be equal to 00000015:000000ef:0002, which is the LSN of the start of the active transaction.

Current LSN             Operation        Context   Transaction ID
----------------------- ---------------- --------- --------------
00000015:000000ef:0002  LOP_BEGIN_XACT   LCX_NULL  0000:00000239
00000016:00000100:00d7  LOP_BEGIN_CKPT   LCX_NULL  0000:00000000
00000016:00000123:0001  LOP_XACT_CKPT    LCX_NULL  0000:00000000
00000016:00000123:0002  LOP_END_CKPT     LCX_NULL  0000:00000000

Also, 00000015:000000ef:0002 (LOP_BEGIN_XACT) is recorded in VLF 2 and the LSN of the CHECKPOINT end (LOP_END_CKPT), 00000016:00000123:0002, is recorded in VLF 3. So, now, both VLF 2 and VLF 3 have LSN entries greater than the MinLSN; and hence a Log Backup should not be able to truncate the VLFs. Lets verify:

BACKUP LOG TLogTruncationDemo
    TO DISK = 'F:\TLogTruncationDemo\TLogTruncationDemo_LogBackup_02.trn'
GO

Now, commit the transaction and then manually fire a CHECKPOINT. Take a Log Backup, and check the status of the log once again:

COMMIT TRAN Demo_Tran_02
GO
CHECKPOINT
GO


BACKUP LOG TLogTruncationDemo
    TO DISK = 'F:\TLogTruncationDemo\TLogTruncationDemo_LogBackup_03.trn'
GO
DBCC LOGINFO(TLogTruncationDemo)
GO

EXPERIMENT 3 :: CHECKPOINT, COMMIT, BACKUP LOG

In this experiment, lets begin a new transaction and insert some rows, so that the entries start from VLF 3 and continue into VLF 4. The commands and the result is as below:

At this point, the MinLSN is the again the begin LSN of the start of the transaction Demo_Tran_03. This is because, it is the minimum of the starting LSN of the active transaction and the starting LSN of the last CHEKCPOINT process. Now, if we commit the transaction, and then take a Log Backup, what do we expect?

According to our theory, since both VLF 3 & 4 contain entries that are greater than the MinLSN, both should remain active, isn’t it? Lets check…

 
What’s going on? Actually behind the covers, whenever you take a backup (Full, Differential or Log Backup), SQL Server fires a CHECKPOINT again. This CHECKPOINT, obviously, comes after the Commit of the Transaction, and hence, the MinLSN for this CHECKPOINT is equal to the starting LSN of the CHECKPOINT. Now, things fall in place, right?

EXPERIMENT 4 :: WRAPPING AROUND THE LOG FILE

Now, let us execute the following queries:

BEGIN TRAN Demo_Tran_04
GO
INSERT INTO TblTLogDEMO (C2, C3) VALUES (1, 'A')
GO 5000
COMMIT TRAN Demo_Tran_04
GO
DBCC LOGINFO(TLogTruncationDemo)
GO
BACKUP LOG TLogTruncationDemo
    TO DISK = 'F:\TLogTruncationDemo\TLogTruncationDemo_LogBackup_05.trn'
GO
DBCC LOGINFO(TLogTruncationDemo)
GO

We can see here that the Log File has wrapped around. How do we know? We know that the transactions have wrapped around the T-Log File by examining the FSeqNo column in the DBCC LOGINFO output. Till the end of Experiment 3, the FSeqNo column for VLF 1 had a value of 20, and now, it has been changed to 24, i.e., FSeqNo for VLF 4 + 1. This shows us that this VLF is being used after VLF 4 was full.

CONCLUSION:

Hence, when in the article Transaction Log Physical Architecture, we say “Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN)”, what we essentially mean is:
While truncating the Transaction Log, we delete all records with “Current LSN” less than the MinLSN. At this point, any VLF that becomes completely empty, i.e., VLFs that consists only of LSN entries less than the MinLSN, is marked as free (inactive).

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.