Learning never stops for a consultant. In this blog, I would share another learning and discuss message: cannot shrink log file because the logical log file located at the end of the file is in use.
Here are some earlier blogs on the same topic where I received messages while trying to shrink the LDF files of the database.
- SQL SERVER – Cannot Shrink Log File Because Total Number of Logical Log Files Cannot be Fewer than 2
- SQL SERVER – Information Message: Cannot shrink file in the database to xxxx pages as it only contains yyyy pages.
The whole shrink story started from below blog.
Many readers have sent me emails with various messages they have seen, and I am writing a blog on them. Following is the information message which might come when you try to shrink the LDF file.
Cannot shrink log file 2 (SQLAuthorityDB_log) because the logical log file located at the end of the file is in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
10 2 1817448 128 1817448 128
We need to use check the status column of VLFs using
USE DATABASE_NAME GO DBCC LOGINFO GO
More information can be found here.
Mostly you would see last VLF having status column value as 2, which means active VLF and can’t be released. Next, we should use the following query:
select name, log_reuse_wait_desc from sys.databases
Based on the output you need to take action and free up the active VLF located at the end. Refer SQL SERVER – How to Stop Growing Log File Too Big
Reference: Pinal Dave (https://blog.sqlauthority.com)