# Sql-server – SQL Server does not shrink log file to target size right after log file grows and differential backup is taken

sql servertransaction-log

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;

--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

IF OBJECT_ID('dbo.Sales') IS NOT NULL
DROP TABLE dbo.sales
CREATE TABLE dbo.Sales
(
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int]  NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL CONSTRAINT [DF_SalesOrderDetail_UnitPriceDiscount]  DEFAULT ((0.0)),
[ModifiedDate] [datetime] NOT NULL,
)

--Load Table to grow the log file (Produce VLFs)
INSERT INTO dbo.Sales
SELECT TOP (350)
SalesOrderID,
SalesOrderDetailID,
CarrierTrackingNumber,
OrderQty,
ProductID,
SpecialOfferID,
UnitPrice,
UnitPriceDiscount,
ModifiedDate
GO 30

--More VLFs produced and they are all active with status 2
USE ShrinkLog;

--RecoveryUnitId    FileId  FileSize    StartOffset FSeqNo  Status  Parity  CreateLSN
--0                 2       253952      8192        33      2       64      0
--0                 2       253952      262144      34      2       64      0
--0                 2       253952      516096      35      2       64      0
--0                 2       278528      770048      36      2       64      0
--0                 2       253952      1048576     37      2       64      35000000030800013
--0                 2       270336      1302528     38      2       64      35000000030800013
--0                 2       253952      1572864     39      2       64      37000000021800364
--0                 2       270336      1826816     40      2       64      37000000021800364
--0                 2       253952      2097152     41      2       64      39000000026300360
--0                 2       270336      2351104     0       0       0       39000000026300360

--Switch to simple recovery mode to truncate logs
ALTER DATABASE ShrinkLog
SET RECOVERY SIMPLE

--Switch back to full
ALTER DATABASE ShrinkLog
SET RECOVERY FULL

--See if log gets truncated? Yes, status becomes 0
USE ShrinkLog;

--RecoveryUnitId    FileId  FileSize    StartOffset FSeqNo  Status  Parity  CreateLSN
--0                 2       253952      8192        33      0       64      0
--0                 2       253952      262144      34      0       64      0
--0                 2       253952      516096      35      0       64      0
--0                 2       278528      770048      36      0       64      0
--0                 2       253952      1048576     37      0       64      35000000030800013
--0                 2       270336      1302528     38      0       64      35000000030800013
--0                 2       253952      1572864     39      0       64      37000000021800364
--0                 2       270336      1826816     40      0       64      37000000021800364
--0                 2       253952      2097152     41      2       64      39000000026300360
--0                 2       270336      2351104     0       0       0       39000000026300360

--Take a diffrential backup to bridge the backup chain
BACKUP DATABASE ShrinkLog
TO DISK = 'NUL'
WITH INIT, DIFFERENTIAL, STATS = 5
GO

--Shrinking does not work
DBCC SHRINKFILE(2)

--DbId  FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
--18    2       287         128         280         128

--Checking the log, VLFs can be reused but cannot be shrunk, strange...
USE ShrinkLog;
--RecoveryUnitId    FileId  FileSize    StartOffset FSeqNo  Status  Parity  CreateLSN
--0                 2       253952      8192        42      2       128     0
--0                 2       253952      262144      34      0       64      0
--0                 2       253952      516096      35      0       64      0
--0                 2       278528      770048      36      0       64      0
--0                 2       253952      1048576     37      0       64      35000000030800013
--0                 2       270336      1302528     38      0       64      35000000030800013
--0                 2       253952      1572864     39      0       64      37000000021800364
--0                 2       270336      1826816     40      0       64      37000000021800364
--0                 2       253952      2097152     41      2       64      39000000026300360

--Take a log backup
BACKUP LOG ShrinkLog
TO DISK = 'NUL'
WITH NOINIT, STATS = 5
GO

--Check Log, VLF 41 turns inactive
USE ShrinkLog;
--RecoveryUnitId    FileId  FileSize    StartOffset FSeqNo  Status  Parity  CreateLSN
--0                 2       253952      8192        42      2       128     0
--0                 2       253952      262144      34      0       64      0
--0                 2       253952      516096      35      0       64      0
--0                 2       278528      770048      36      0       64      0
--0                 2       253952      1048576     37      0       64      35000000030800013
--0                 2       270336      1302528     38      0       64      35000000030800013
--0                 2       253952      1572864     39      0       64      37000000021800364
--0                 2       270336      1826816     40      0       64      37000000021800364
--0                 2       253952      2097152     41      0       64      39000000026300360

--Then you are able to shrink
DBCC SHRINKFILE(2)
GO
--DbId  FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
--18    2       128         128         128         128