Since I wrote my article about the script of shrinking the Log Files, there have been many emails from my blog readers about various messages which they are seeing during shrink. I have been sharing them with other blog readers as well. In this blog, we would discuss a message Cannot shrink log file because a total number of logical log files cannot be fewer than 2.
First, go through my earlier blog to understand the context.
Here is the exact message which was reported by 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
(1 row affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note that this message would appear when we provide the desired size of the file as follows.
DBCC SHRINKFILE ('<LDF Logical Name>',<some size>);
Here is the complete script to reproduce the message.
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);
What is the meaning of the message? If I run DBCC LOGINFO, you can see the output.
NOTE: I got the message in SQL Server 2017 but not in SQL 2008.
Which means that we already have only 2 VLFs in the database so SQL can’t do anything further to shrink the file and hence the message.
If you have such message and LDF file size is huge (by only 2 VLFs) then you need to find a way to get rid of those big VLFs. I think this can be done by increasing the LDF size and make sure that Status becomes zero for those VLFs. If you have such a situation, please share your solution via comments to help others.
Reference: Pinal Dave (https://blog.sqlauthority.com)