SQL Server does not shrink log file to target size right after log file grows and differential backup is taken

I was trying to shrink a highly fragmented log file that had already grown beyond its default size.

Firstly, I switched the database from FULL to SIMPLE recovery mode, then backed to full for log truncation. This worked fine and the logs were truncated successfully.

After that, I did not shrink my log file immediately. Instead, I took a differential backup for bridging the recovery chain.

After that, when I was trying to shrink my log file, it seemed that the file appeared not to be shrunk, unless you take a log backup before performing the shrink.

So what is the reason behind this? The VLFs are truncated, so why can't they be shrunk?

The script below explains each step I took:

```
USE Master;
GO

IF DB_ID('ShrinkLog') IS NOT NULL
BEGIN
ALTER DATABASE ShrinkLog
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE ShrinkLog;
END
GO

--Create a database for test
CREATE DATABASE ShrinkLog
ON PRIMARY
(NAME = N'ShrinkLog_Date', FILENAME = N'C:\SQLDATA\ShrinkLog_Data.Mdf', SIZE = 1024MB, MAXSIZE = UNLIMITED, FILEGROWTH = 50MB)
LOG ON
(NAME = N'ShrinkLog_Log', FILENAME = N'C:\SQLDATA\ShrinkLog_Log.LDF', SIZE = 1MB, MAXSIZE = 3GB, FILEGROWTH = 512kb);
GO

--Set recovery mode to full
ALTER DATABASE ShrinkLog SET RECOVERY FULL;

--Check VLFs
USE ShrinkLog;
DBCC LOGINFO;

--RecoveryUnitId FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
--0 2 253952 8192 33 2 64 0
--0 2 253952 262144 0 0 0 0
--0 2 253952 516096 0 0 0 0
--0 2 278528 770048 0 0 0 0

--Take a full backup to make sure it is in full recovery mode
BACKUP DATABASE ShrinkLog
TO DISK = 'NUL' WITH INIT, FORMAT
GO

--Create a table to load
IF OBJECT_ID('dbo.Sales') IS NOT NULL
DROP TABLE dbo.sales
CREATE TABLE dbo.Sales
(
[SalesOrderID] [int] NOT NULL,
[SalesO

Log files are written to sequentially in a round-robin fashion. The log won't be shrunk beyond the last active portion of the log (in your example above, VLF 41). Once you take a log backup, that VLF is cleared, and the file 'round-robins' back to the first inactive VLF, at which point you can shrink to that one.

https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide

There shouldn't really be any need to change recovery model to achieve this. Either take regular log backups, or if you don't require point-in-time recovery, move to simple recovery.

Finally, once you've shrunk the fragmented log file, grow it to a suitable size with sensible autogrowth settings. It's likely fragmented because it was never sized correctly (1MB?) and has had to grow frequently in (10% ?) steps. Log growth is a slow process and so you want to avoid it for this reason in addition to it causing fragmentation.

(Points 6,7,8 here)
https://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

(No. of VLF's)
https://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

StackExchange Database Administrators Q#196302, answer score: 2