Since the release of my article on shrinking log files in SQL Server, I have received numerous emails from readers who have encountered various messages during the shrink process. In this blog post, we will discuss one specific message: “Cannot shrink log file because a total number of logical log files cannot be fewer than 2.”

Before we dive into the details, I recommend reading my previous blog post on how to shrink all log files for SQL Server, as it will provide the necessary context. You can find it here: [insert link to previous blog post]

Let’s take a look at the exact message that was reported by the DBCC SHRINKFILE command:

Cannot shrink log file 2 (DB_Shrink_Test_log) because total number of logical log files cannot be fewer than 2.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
--- -------- ----------- ----------- --------- --------------
8   2        497         497         496       496

This message appears when we provide a desired size for the file using the following command:

DBCC SHRINKFILE ('LDF Logical Name','some size');

To reproduce this message, you can use the following script:

USE [master]
GO

IF DB_ID('DB_Shrink_Test') IS NOT NULL
BEGIN
	ALTER DATABASE [DB_Shrink_Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	DROP DATABASE [DB_Shrink_Test]
END
GO

CREATE DATABASE [DB_Shrink_Test]
GO

USE [DB_Shrink_Test]; 
CHECKPOINT; 
DBCC SHRINKFILE ('DB_Shrink_Test_log',1);

So, what does this message mean? If we run the DBCC LOGINFO command, we can see the output. It’s important to note that I encountered this message in SQL Server 2017, but not in SQL Server 2008. This indicates that we already have only 2 Virtual Log Files (VLFs) in the database, and SQL Server cannot further shrink the file. Hence, the message.

If you encounter this message and the LDF file size is still large (due to only 2 VLFs), you need to find a way to eliminate those large VLFs. One possible solution is to increase the LDF size and ensure that the status of those VLFs becomes zero. If you have faced a similar situation and found a solution, please share it in the comments to help others. You can also reach out to me on Twitter for further discussion.

Thank you for reading and stay tuned for more SQL Server tips and tricks!