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,
[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
FROM AdventureWorks2012.Sales.SalesOrderDetail
GO 30
--More VLFs produced and they are all active with status 2
USE ShrinkLog;
DBCC LOGINFO
--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;
DBCC LOGINFO
--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;
DBCC LOGINFO
--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;
DBCC LOGINFO
--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
Best Answer
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://docs.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/