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

Question

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

Answer 1

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/

Answer 2

Sometimes I encounter the same issue and the answer of Henry van Megen is the really last step I would ever take to fix this issue. Luckily this never happens to me.

When I encounter this problem, I've got this checklist:

1.) Shrink Files, but just with [Database]->[Tasks]->[Shrink]->[Files] (You did this with a script)

2.) Backup (Full- and Logbackup)

For my Full Backup, I take this snippet:

BACKUP DATABASE [DATABASE]
TO  DISK = @Path
WITH NOFORMAT, INIT,
NAME = N'BACKUP EVERYTHING',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

For my Log Backup, I take this snippet:

BACKUP LOG [DATABASE] 
TO  DISK = @Path
WITH NOFORMAT, NOINIT,  
NAME = N'BACKUP LOG', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Disclaimer: This might not be a best case backup-scripts in your scenario.

3.) After Backup, I shrink again, but this time, with this script.

USE [Database]
GO
DBCC SHRINKFILE (N'Databaselogfilename' , 15000)
GO

In many strange cases, this will not help (as your case in example, because you did this). The next Script with the "TRUNCATEONLY" Keyword was my savior.

USE [Database]
GO
DBCC SHRINKFILE (N'Databaselogfilename' , 0, TRUNCATEONLY)
GO

In the most cases, this will shrink the files.

But there was two cases in my years of SQL Server experience, even this did not help. This was the way I solved the cases

1.) Backups (Log, Full) (Use the snippets I provide)

2.) Change Recovery Model to Simple

3.) Backups (Log, Full) (yes, again) (Use the snippets I provide)

4.) Did the file shrink?

5.) Yes -> Change Recovery Model to Full (or Bulk, whatever you had) again

6.) No -> Repeat Backups, Follow with Step 7

7.) Kill all Sessions

8.) Backups again, Full and Log (Use the snippets I provide)

Now the files should be small. If not, do the method recommended by Henry. This will be the last thing

Answer 3

The best option I always find when confronted with issues like this is just to restart the service, switch to single user mode, export the database, delete the original and import it back where it belongs.

In over 15+ years of working with SQL server I have come across 4 instances where this was the only way to fix the issue.

Every DBMS has it's quirks.. don't get me started about those of MySQL or PostgreSQL